diff options
28 files changed, 1306 insertions, 65 deletions
diff --git a/django/contrib/postgres/apps.py b/django/contrib/postgres/apps.py index 25cfa1a814..781c8728f2 100644 --- a/django/contrib/postgres/apps.py +++ b/django/contrib/postgres/apps.py @@ -6,10 +6,13 @@ from django.apps import AppConfig from django.db import connections from django.db.backends.signals import connection_created from django.db.migrations.writer import MigrationWriter -from django.db.models import CharField, TextField +from django.db.models import CharField, OrderBy, TextField +from django.db.models.functions import Collate +from django.db.models.indexes import IndexExpression from django.test.signals import setting_changed from django.utils.translation import gettext_lazy as _ +from .indexes import OpClass from .lookups import SearchLookup, TrigramSimilar, Unaccent from .serializers import RangeSerializer from .signals import register_type_handlers @@ -63,3 +66,4 @@ class PostgresConfig(AppConfig): CharField.register_lookup(TrigramSimilar) TextField.register_lookup(TrigramSimilar) MigrationWriter.register_serializer(RANGE_TYPES, RangeSerializer) + IndexExpression.register_wrappers(OrderBy, OpClass, Collate) diff --git a/django/contrib/postgres/indexes.py b/django/contrib/postgres/indexes.py index c2e29e5298..af9de758f7 100644 --- a/django/contrib/postgres/indexes.py +++ b/django/contrib/postgres/indexes.py @@ -1,5 +1,5 @@ from django.db import NotSupportedError -from django.db.models import Index +from django.db.models import Func, Index from django.utils.functional import cached_property __all__ = [ @@ -39,8 +39,8 @@ class PostgresIndex(Index): class BloomIndex(PostgresIndex): suffix = 'bloom' - def __init__(self, *, length=None, columns=(), **kwargs): - super().__init__(**kwargs) + def __init__(self, *expressions, length=None, columns=(), **kwargs): + super().__init__(*expressions, **kwargs) if len(self.fields) > 32: raise ValueError('Bloom indexes support a maximum of 32 fields.') if not isinstance(columns, (list, tuple)): @@ -83,12 +83,12 @@ class BloomIndex(PostgresIndex): class BrinIndex(PostgresIndex): suffix = 'brin' - def __init__(self, *, autosummarize=None, pages_per_range=None, **kwargs): + def __init__(self, *expressions, autosummarize=None, pages_per_range=None, **kwargs): if pages_per_range is not None and pages_per_range <= 0: raise ValueError('pages_per_range must be None or a positive integer') self.autosummarize = autosummarize self.pages_per_range = pages_per_range - super().__init__(**kwargs) + super().__init__(*expressions, **kwargs) def deconstruct(self): path, args, kwargs = super().deconstruct() @@ -114,9 +114,9 @@ class BrinIndex(PostgresIndex): class BTreeIndex(PostgresIndex): suffix = 'btree' - def __init__(self, *, fillfactor=None, **kwargs): + def __init__(self, *expressions, fillfactor=None, **kwargs): self.fillfactor = fillfactor - super().__init__(**kwargs) + super().__init__(*expressions, **kwargs) def deconstruct(self): path, args, kwargs = super().deconstruct() @@ -134,10 +134,10 @@ class BTreeIndex(PostgresIndex): class GinIndex(PostgresIndex): suffix = 'gin' - def __init__(self, *, fastupdate=None, gin_pending_list_limit=None, **kwargs): + def __init__(self, *expressions, fastupdate=None, gin_pending_list_limit=None, **kwargs): self.fastupdate = fastupdate self.gin_pending_list_limit = gin_pending_list_limit - super().__init__(**kwargs) + super().__init__(*expressions, **kwargs) def deconstruct(self): path, args, kwargs = super().deconstruct() @@ -159,10 +159,10 @@ class GinIndex(PostgresIndex): class GistIndex(PostgresIndex): suffix = 'gist' - def __init__(self, *, buffering=None, fillfactor=None, **kwargs): + def __init__(self, *expressions, buffering=None, fillfactor=None, **kwargs): self.buffering = buffering self.fillfactor = fillfactor - super().__init__(**kwargs) + super().__init__(*expressions, **kwargs) def deconstruct(self): path, args, kwargs = super().deconstruct() @@ -188,9 +188,9 @@ class GistIndex(PostgresIndex): class HashIndex(PostgresIndex): suffix = 'hash' - def __init__(self, *, fillfactor=None, **kwargs): + def __init__(self, *expressions, fillfactor=None, **kwargs): self.fillfactor = fillfactor - super().__init__(**kwargs) + super().__init__(*expressions, **kwargs) def deconstruct(self): path, args, kwargs = super().deconstruct() @@ -208,9 +208,9 @@ class HashIndex(PostgresIndex): class SpGistIndex(PostgresIndex): suffix = 'spgist' - def __init__(self, *, fillfactor=None, **kwargs): + def __init__(self, *expressions, fillfactor=None, **kwargs): self.fillfactor = fillfactor - super().__init__(**kwargs) + super().__init__(*expressions, **kwargs) def deconstruct(self): path, args, kwargs = super().deconstruct() @@ -223,3 +223,10 @@ class SpGistIndex(PostgresIndex): if self.fillfactor is not None: with_params.append('fillfactor = %d' % self.fillfactor) return with_params + + +class OpClass(Func): + template = '%(expressions)s %(name)s' + + def __init__(self, expression, name): + super().__init__(expression, name=name) diff --git a/django/db/backends/base/features.py b/django/db/backends/base/features.py index 42eb59c969..7e22f879ba 100644 --- a/django/db/backends/base/features.py +++ b/django/db/backends/base/features.py @@ -281,6 +281,10 @@ class BaseDatabaseFeatures: supports_functions_in_partial_indexes = True # Does the backend support covering indexes (CREATE INDEX ... INCLUDE ...)? supports_covering_indexes = False + # Does the backend support indexes on expressions? + supports_expression_indexes = False + # Does the backend treat COLLATE as an indexed expression? + collate_as_index_expression = False # Does the database allow more than one constraint or index on the same # field(s)? diff --git a/django/db/backends/base/schema.py b/django/db/backends/base/schema.py index 2f05ae5d96..be33ab3e4d 100644 --- a/django/db/backends/base/schema.py +++ b/django/db/backends/base/schema.py @@ -2,10 +2,11 @@ import logging from datetime import datetime from django.db.backends.ddl_references import ( - Columns, ForeignKeyName, IndexName, Statement, Table, + Columns, Expressions, ForeignKeyName, IndexName, Statement, Table, ) from django.db.backends.utils import names_digest, split_identifier from django.db.models import Deferrable, Index +from django.db.models.sql import Query from django.db.transaction import TransactionManagementError, atomic from django.utils import timezone @@ -354,10 +355,20 @@ class BaseDatabaseSchemaEditor: def add_index(self, model, index): """Add an index on a model.""" + if ( + index.contains_expressions and + not self.connection.features.supports_expression_indexes + ): + return None self.execute(index.create_sql(model, self), params=None) def remove_index(self, model, index): """Remove an index from a model.""" + if ( + index.contains_expressions and + not self.connection.features.supports_expression_indexes + ): + return None self.execute(index.remove_sql(model, self)) def add_constraint(self, model, constraint): @@ -992,12 +1003,17 @@ class BaseDatabaseSchemaEditor: def _create_index_sql(self, model, *, fields=None, name=None, suffix='', using='', db_tablespace=None, col_suffixes=(), sql=None, opclasses=(), - condition=None, include=None): + condition=None, include=None, expressions=None): """ - Return the SQL statement to create the index for one or several fields. - `sql` can be specified if the syntax differs from the standard (GIS - indexes, ...). + Return the SQL statement to create the index for one or several fields + or expressions. `sql` can be specified if the syntax differs from the + standard (GIS indexes, ...). """ + fields = fields or [] + expressions = expressions or [] + compiler = Query(model, alias_cols=False).get_compiler( + connection=self.connection, + ) tablespace_sql = self._get_index_tablespace_sql(model, fields, db_tablespace=db_tablespace) columns = [field.column for field in fields] sql_create_index = sql or self.sql_create_index @@ -1014,7 +1030,11 @@ class BaseDatabaseSchemaEditor: table=Table(table, self.quote_name), name=IndexName(table, columns, suffix, create_index_name), using=using, - columns=self._index_columns(table, columns, col_suffixes, opclasses), + columns=( + self._index_columns(table, columns, col_suffixes, opclasses) + if columns + else Expressions(table, expressions, compiler, self.quote_value) + ), extra=tablespace_sql, condition=self._index_condition_sql(condition), include=self._index_include_sql(model, include), @@ -1046,7 +1066,11 @@ class BaseDatabaseSchemaEditor: output.append(self._create_index_sql(model, fields=fields, suffix='_idx')) for index in model._meta.indexes: - output.append(index.create_sql(model, self)) + if ( + not index.contains_expressions or + self.connection.features.supports_expression_indexes + ): + output.append(index.create_sql(model, self)) return output def _field_indexes_sql(self, model, field): diff --git a/django/db/backends/ddl_references.py b/django/db/backends/ddl_references.py index ba55de1df8..c06386a2fa 100644 --- a/django/db/backends/ddl_references.py +++ b/django/db/backends/ddl_references.py @@ -2,6 +2,7 @@ Helpers to manipulate deferred DDL statements that might need to be adjusted or discarded within when executing a migration. """ +from copy import deepcopy class Reference: @@ -198,3 +199,38 @@ class Statement(Reference): def __str__(self): return self.template % self.parts + + +class Expressions(TableColumns): + def __init__(self, table, expressions, compiler, quote_value): + self.compiler = compiler + self.expressions = expressions + self.quote_value = quote_value + columns = [col.target.column for col in self.compiler.query._gen_cols([self.expressions])] + super().__init__(table, columns) + + def rename_table_references(self, old_table, new_table): + if self.table != old_table: + return + expressions = deepcopy(self.expressions) + self.columns = [] + for col in self.compiler.query._gen_cols([expressions]): + col.alias = new_table + self.expressions = expressions + super().rename_table_references(old_table, new_table) + + def rename_column_references(self, table, old_column, new_column): + if self.table != table: + return + expressions = deepcopy(self.expressions) + self.columns = [] + for col in self.compiler.query._gen_cols([expressions]): + if col.target.column == old_column: + col.target.column = new_column + self.columns.append(col.target.column) + self.expressions = expressions + + def __str__(self): + sql, params = self.compiler.compile(self.expressions) + params = map(self.quote_value, params) + return sql % tuple(params) diff --git a/django/db/backends/mysql/features.py b/django/db/backends/mysql/features.py index 0840a4cf6b..419b2ba6f0 100644 --- a/django/db/backends/mysql/features.py +++ b/django/db/backends/mysql/features.py @@ -41,6 +41,10 @@ class DatabaseFeatures(BaseDatabaseFeatures): """ # Neither MySQL nor MariaDB support partial indexes. supports_partial_indexes = False + # COLLATE must be wrapped in parentheses because MySQL treats COLLATE as an + # indexed expression. + collate_as_index_expression = True + supports_order_by_nulls_modifier = False order_by_nulls_first = True test_collations = { @@ -60,6 +64,10 @@ class DatabaseFeatures(BaseDatabaseFeatures): 'model_fields.test_textfield.TextFieldTests.test_emoji', 'model_fields.test_charfield.TestCharField.test_emoji', }, + "MySQL doesn't support functional indexes on a function that " + "returns JSON": { + 'schema.tests.SchemaTests.test_func_index_json_key_transform', + }, } if 'ONLY_FULL_GROUP_BY' in self.connection.sql_mode: skips.update({ @@ -225,3 +233,10 @@ class DatabaseFeatures(BaseDatabaseFeatures): not self.connection.mysql_is_mariadb and self.connection.mysql_version >= (8, 0, 1) ) + + @cached_property + def supports_expression_indexes(self): + return ( + not self.connection.mysql_is_mariadb and + self.connection.mysql_version >= (8, 0, 13) + ) diff --git a/django/db/backends/oracle/features.py b/django/db/backends/oracle/features.py index 2570675809..bedc398517 100644 --- a/django/db/backends/oracle/features.py +++ b/django/db/backends/oracle/features.py @@ -59,6 +59,7 @@ class DatabaseFeatures(BaseDatabaseFeatures): supports_ignore_conflicts = False max_query_params = 2**16 - 1 supports_partial_indexes = False + supports_expression_indexes = True supports_slicing_ordering_in_compound = True allows_multiple_constraints_on_same_fields = False supports_boolean_expr_in_select_clause = False diff --git a/django/db/backends/postgresql/features.py b/django/db/backends/postgresql/features.py index 84259c0c19..b7ec377739 100644 --- a/django/db/backends/postgresql/features.py +++ b/django/db/backends/postgresql/features.py @@ -58,6 +58,7 @@ class DatabaseFeatures(BaseDatabaseFeatures): supports_deferrable_unique_constraints = True has_json_operators = True json_key_contains_list_matching_requires_list = True + supports_expression_indexes = True django_test_skips = { 'opclasses are PostgreSQL only.': { diff --git a/django/db/backends/postgresql/schema.py b/django/db/backends/postgresql/schema.py index 13dd99adb0..f3b5baecbe 100644 --- a/django/db/backends/postgresql/schema.py +++ b/django/db/backends/postgresql/schema.py @@ -227,11 +227,12 @@ class DatabaseSchemaEditor(BaseDatabaseSchemaEditor): def _create_index_sql( self, model, *, fields=None, name=None, suffix='', using='', db_tablespace=None, col_suffixes=(), sql=None, opclasses=(), - condition=None, concurrently=False, include=None, + condition=None, concurrently=False, include=None, expressions=None, ): sql = self.sql_create_index if not concurrently else self.sql_create_index_concurrently return super()._create_index_sql( model, fields=fields, name=name, suffix=suffix, using=using, db_tablespace=db_tablespace, col_suffixes=col_suffixes, sql=sql, opclasses=opclasses, condition=condition, include=include, + expressions=expressions, ) diff --git a/django/db/backends/sqlite3/features.py b/django/db/backends/sqlite3/features.py index 3348256c74..31055f8f99 100644 --- a/django/db/backends/sqlite3/features.py +++ b/django/db/backends/sqlite3/features.py @@ -38,6 +38,7 @@ class DatabaseFeatures(BaseDatabaseFeatures): supports_pragma_foreign_key_check = Database.sqlite_version_info >= (3, 20, 0) can_defer_constraint_checks = supports_pragma_foreign_key_check supports_functions_in_partial_indexes = Database.sqlite_version_info >= (3, 15, 0) + supports_expression_indexes = True supports_over_clause = Database.sqlite_version_info >= (3, 25, 0) supports_frame_range_fixed_distance = Database.sqlite_version_info >= (3, 28, 0) supports_aggregate_filter_clause = Database.sqlite_version_info >= (3, 30, 1) diff --git a/django/db/backends/sqlite3/introspection.py b/django/db/backends/sqlite3/introspection.py index 1d7ce3fabf..8c1d4582ab 100644 --- a/django/db/backends/sqlite3/introspection.py +++ b/django/db/backends/sqlite3/introspection.py @@ -416,9 +416,9 @@ class DatabaseIntrospection(BaseDatabaseIntrospection): if constraints[index]['index'] and not constraints[index]['unique']: # SQLite doesn't support any index type other than b-tree constraints[index]['type'] = Index.suffix - order_info = sql.split('(')[-1].split(')')[0].split(',') - orders = ['DESC' if info.endswith('DESC') else 'ASC' for info in order_info] - constraints[index]['orders'] = orders + orders = self._get_index_columns_orders(sql) + if orders is not None: + constraints[index]['orders'] = orders # Get the PK pk_column = self.get_primary_key_column(cursor, table_name) if pk_column: @@ -437,6 +437,14 @@ class DatabaseIntrospection(BaseDatabaseIntrospection): constraints.update(self._get_foreign_key_constraints(cursor, table_name)) return constraints + def _get_index_columns_orders(self, sql): + tokens = sqlparse.parse(sql)[0] + for token in tokens: + if isinstance(token, sqlparse.sql.Parenthesis): + columns = str(token).strip('()').split(', ') + return ['DESC' if info.endswith('DESC') else 'ASC' for info in columns] + return None + def _get_column_collations(self, cursor, table_name): row = cursor.execute(""" SELECT sql diff --git a/django/db/migrations/operations/models.py b/django/db/migrations/operations/models.py index 7e8becb100..28a7dde4dc 100644 --- a/django/db/migrations/operations/models.py +++ b/django/db/migrations/operations/models.py @@ -777,6 +777,12 @@ class AddIndex(IndexOperation): ) def describe(self): + if self.index.expressions: + return 'Create index %s on %s on model %s' % ( + self.index.name, + ', '.join([str(expression) for expression in self.index.expressions]), + self.model_name, + ) return 'Create index %s on field(s) %s of model %s' % ( self.index.name, ', '.join(self.index.fields), diff --git a/django/db/models/base.py b/django/db/models/base.py index 822aad080d..f9ec636235 100644 --- a/django/db/models/base.py +++ b/django/db/models/base.py @@ -1681,6 +1681,22 @@ class Model(metaclass=ModelBase): id='models.W040', ) ) + if not ( + connection.features.supports_expression_indexes or + 'supports_expression_indexes' in cls._meta.required_db_features + ) and any(index.contains_expressions for index in cls._meta.indexes): + errors.append( + checks.Warning( + '%s does not support indexes on expressions.' + % connection.display_name, + hint=( + "An index won't be created. Silence this warning " + "if you don't care about it." + ), + obj=cls, + id='models.W043', + ) + ) fields = [field for index in cls._meta.indexes for field, _ in index.fields_orders] fields += [include for index in cls._meta.indexes for include in index.include] errors.extend(cls._check_local_fields(fields, 'indexes')) diff --git a/django/db/models/expressions.py b/django/db/models/expressions.py index 48f295941d..ffc3a7fda5 100644 --- a/django/db/models/expressions.py +++ b/django/db/models/expressions.py @@ -375,7 +375,10 @@ class BaseExpression: yield self for expr in self.get_source_expressions(): if expr: - yield from expr.flatten() + if hasattr(expr, 'flatten'): + yield from expr.flatten() + else: + yield expr def select_format(self, compiler, sql, params): """ @@ -897,6 +900,10 @@ class ExpressionList(Func): def __str__(self): return self.arg_joiner.join(str(arg) for arg in self.source_expressions) + def as_sqlite(self, compiler, connection, **extra_context): + # Casting to numeric is unnecessary. + return self.as_sql(compiler, connection, **extra_context) + class ExpressionWrapper(Expression): """ diff --git a/django/db/models/indexes.py b/django/db/models/indexes.py index fddde3b16f..5530d0b661 100644 --- a/django/db/models/indexes.py +++ b/django/db/models/indexes.py @@ -1,6 +1,9 @@ from django.db.backends.utils import names_digest, split_identifier +from django.db.models.expressions import Col, ExpressionList, F, Func, OrderBy +from django.db.models.functions import Collate from django.db.models.query_utils import Q from django.db.models.sql import Query +from django.utils.functional import partition __all__ = ['Index'] @@ -13,7 +16,7 @@ class Index: def __init__( self, - *, + *expressions, fields=(), name=None, db_tablespace=None, @@ -31,11 +34,25 @@ class Index: raise ValueError('Index.fields must be a list or tuple.') if not isinstance(opclasses, (list, tuple)): raise ValueError('Index.opclasses must be a list or tuple.') + if not expressions and not fields: + raise ValueError( + 'At least one field or expression is required to define an ' + 'index.' + ) + if expressions and fields: + raise ValueError( + 'Index.fields and expressions are mutually exclusive.', + ) + if expressions and not name: + raise ValueError('An index must be named to use expressions.') + if expressions and opclasses: + raise ValueError( + 'Index.opclasses cannot be used with expressions. Use ' + 'django.contrib.postgres.indexes.OpClass() instead.' + ) if opclasses and len(fields) != len(opclasses): raise ValueError('Index.fields and Index.opclasses must have the same number of elements.') - if not fields: - raise ValueError('At least one field is required to define an index.') - if not all(isinstance(field, str) for field in fields): + if fields and not all(isinstance(field, str) for field in fields): raise ValueError('Index.fields must contain only strings with field names.') if include and not name: raise ValueError('A covering index must be named.') @@ -52,6 +69,14 @@ class Index: self.opclasses = opclasses self.condition = condition self.include = tuple(include) if include else () + self.expressions = tuple( + F(expression) if isinstance(expression, str) else expression + for expression in expressions + ) + + @property + def contains_expressions(self): + return bool(self.expressions) def _get_condition_sql(self, model, schema_editor): if self.condition is None: @@ -63,15 +88,31 @@ class Index: return sql % tuple(schema_editor.quote_value(p) for p in params) def create_sql(self, model, schema_editor, using='', **kwargs): - fields = [model._meta.get_field(field_name) for field_name, _ in self.fields_orders] include = [model._meta.get_field(field_name).column for field_name in self.include] - col_suffixes = [order[1] for order in self.fields_orders] condition = self._get_condition_sql(model, schema_editor) + if self.expressions: + index_expressions = [] + for expression in self.expressions: + index_expression = IndexExpression(expression) + index_expression.set_wrapper_classes(schema_editor.connection) + index_expressions.append(index_expression) + expressions = ExpressionList(*index_expressions).resolve_expression( + Query(model, alias_cols=False), + ) + fields = None + col_suffixes = None + else: + fields = [ + model._meta.get_field(field_name) + for field_name, _ in self.fields_orders + ] + col_suffixes = [order[1] for order in self.fields_orders] + expressions = None return schema_editor._create_index_sql( model, fields=fields, name=self.name, using=using, db_tablespace=self.db_tablespace, col_suffixes=col_suffixes, opclasses=self.opclasses, condition=condition, include=include, - **kwargs, + expressions=expressions, **kwargs, ) def remove_sql(self, model, schema_editor, **kwargs): @@ -80,7 +121,9 @@ class Index: def deconstruct(self): path = '%s.%s' % (self.__class__.__module__, self.__class__.__name__) path = path.replace('django.db.models.indexes', 'django.db.models') - kwargs = {'fields': self.fields, 'name': self.name} + kwargs = {'name': self.name} + if self.fields: + kwargs['fields'] = self.fields if self.db_tablespace is not None: kwargs['db_tablespace'] = self.db_tablespace if self.opclasses: @@ -89,12 +132,12 @@ class Index: kwargs['condition'] = self.condition if self.include: kwargs['include'] = self.include - return (path, (), kwargs) + return (path, self.expressions, kwargs) def clone(self): """Create a copy of this Index.""" - _, _, kwargs = self.deconstruct() - return self.__class__(**kwargs) + _, args, kwargs = self.deconstruct() + return self.__class__(*args, **kwargs) def set_name_with_model(self, model): """ @@ -126,8 +169,12 @@ class Index: self.name = 'D%s' % self.name[1:] def __repr__(self): - return "<%s: fields='%s'%s%s%s>" % ( - self.__class__.__name__, ', '.join(self.fields), + return '<%s:%s%s%s%s%s>' % ( + self.__class__.__name__, + '' if not self.fields else " fields='%s'" % ', '.join(self.fields), + '' if not self.expressions else " expressions='%s'" % ', '.join([ + str(expression) for expression in self.expressions + ]), '' if self.condition is None else ' condition=%s' % self.condition, '' if not self.include else " include='%s'" % ', '.join(self.include), '' if not self.opclasses else " opclasses='%s'" % ', '.join(self.opclasses), @@ -137,3 +184,84 @@ class Index: if self.__class__ == other.__class__: return self.deconstruct() == other.deconstruct() return NotImplemented + + +class IndexExpression(Func): + """Order and wrap expressions for CREATE INDEX statements.""" + template = '%(expressions)s' + wrapper_classes = (OrderBy, Collate) + + def set_wrapper_classes(self, connection=None): + # Some databases (e.g. MySQL) treats COLLATE as an indexed expression. + if connection and connection.features.collate_as_index_expression: + self.wrapper_classes = tuple([ + wrapper_cls + for wrapper_cls in self.wrapper_classes + if wrapper_cls is not Collate + ]) + + @classmethod + def register_wrappers(cls, *wrapper_classes): + cls.wrapper_classes = wrapper_classes + + def resolve_expression( + self, + query=None, + allow_joins=True, + reuse=None, + summarize=False, + for_save=False, + ): + expressions = list(self.flatten()) + # Split expressions and wrappers. + index_expressions, wrappers = partition( + lambda e: isinstance(e, self.wrapper_classes), + expressions, + ) + wrapper_types = [type(wrapper) for wrapper in wrappers] + if len(wrapper_types) != len(set(wrapper_types)): + raise ValueError( + "Multiple references to %s can't be used in an indexed " + "expression." % ', '.join([ + wrapper_cls.__qualname__ for wrapper_cls in self.wrapper_classes + ]) + ) + if expressions[1:len(wrappers) + 1] != wrappers: + raise ValueError( + '%s must be topmost expressions in an indexed expression.' + % ', '.join([ + wrapper_cls.__qualname__ for wrapper_cls in self.wrapper_classes + ]) + ) + # Wrap expressions in parentheses if they are not column references. + root_expression = index_expressions[1] + resolve_root_expression = root_expression.resolve_expression( + query, + allow_joins, + reuse, + summarize, + for_save, + ) + if not isinstance(resolve_root_expression, Col): + root_expression = Func(root_expression, template='(%(expressions)s)') + + if wrappers: + # Order wrappers and set their expressions. + wrappers = sorted( + wrappers, + key=lambda w: self.wrapper_classes.index(type(w)), + ) + wrappers = [wrapper.copy() for wrapper in wrappers] + for i, wrapper in enumerate(wrappers[:-1]): + wrapper.set_source_expressions([wrappers[i + 1]]) + # Set the root expression on the deepest wrapper. + wrappers[-1].set_source_expressions([root_expression]) + self.set_source_expressions([wrappers[0]]) + else: + # Use the root expression, if there are no wrappers. + self.set_source_expressions([root_expression]) + return super().resolve_expression(query, allow_joins, reuse, summarize, for_save) + + def as_sqlite(self, compiler, connection, **extra_context): + # Casting to numeric is unnecessary. + return self.as_sql(compiler, connection, **extra_context) diff --git a/docs/ref/checks.txt b/docs/ref/checks.txt index a3d1b0664d..b07a81f413 100644 --- a/docs/ref/checks.txt +++ b/docs/ref/checks.txt @@ -381,6 +381,7 @@ Models * **models.E041**: ``constraints`` refers to the joined field ``<field name>``. * **models.W042**: Auto-created primary key used when not defining a primary key type, by default ``django.db.models.AutoField``. +* **models.W043**: ``<database>`` does not support indexes on expressions. Security -------- diff --git a/docs/ref/contrib/postgres/indexes.txt b/docs/ref/contrib/postgres/indexes.txt index f9c0fb2f97..4a9b2ad22e 100644 --- a/docs/ref/contrib/postgres/indexes.txt +++ b/docs/ref/contrib/postgres/indexes.txt @@ -10,7 +10,7 @@ available from the ``django.contrib.postgres.indexes`` module. ``BloomIndex`` ============== -.. class:: BloomIndex(length=None, columns=(), **options) +.. class:: BloomIndex(*expressions, length=None, columns=(), **options) .. versionadded:: 3.1 @@ -30,10 +30,15 @@ available from the ``django.contrib.postgres.indexes`` module. .. _bloom: https://www.postgresql.org/docs/current/bloom.html + .. versionchanged:: 3.2 + + Positional argument ``*expressions`` was added in order to support + functional indexes. + ``BrinIndex`` ============= -.. class:: BrinIndex(autosummarize=None, pages_per_range=None, **options) +.. class:: BrinIndex(*expressions, autosummarize=None, pages_per_range=None, **options) Creates a `BRIN index <https://www.postgresql.org/docs/current/brin-intro.html>`_. @@ -43,24 +48,34 @@ available from the ``django.contrib.postgres.indexes`` module. The ``pages_per_range`` argument takes a positive integer. + .. versionchanged:: 3.2 + + Positional argument ``*expressions`` was added in order to support + functional indexes. + .. _automatic summarization: https://www.postgresql.org/docs/current/brin-intro.html#BRIN-OPERATION ``BTreeIndex`` ============== -.. class:: BTreeIndex(fillfactor=None, **options) +.. class:: BTreeIndex(*expressions, fillfactor=None, **options) Creates a B-Tree index. Provide an integer value from 10 to 100 to the fillfactor_ parameter to tune how packed the index pages will be. PostgreSQL's default is 90. + .. versionchanged:: 3.2 + + Positional argument ``*expressions`` was added in order to support + functional indexes. + .. _fillfactor: https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-STORAGE-PARAMETERS ``GinIndex`` ============ -.. class:: GinIndex(fastupdate=None, gin_pending_list_limit=None, **options) +.. class:: GinIndex(*expressions, fastupdate=None, gin_pending_list_limit=None, **options) Creates a `gin index <https://www.postgresql.org/docs/current/gin.html>`_. @@ -79,13 +94,18 @@ available from the ``django.contrib.postgres.indexes`` module. to tune the maximum size of the GIN pending list which is used when ``fastupdate`` is enabled. + .. versionchanged:: 3.2 + + Positional argument ``*expressions`` was added in order to support + functional indexes. + .. _GIN Fast Update Technique: https://www.postgresql.org/docs/current/gin-implementation.html#GIN-FAST-UPDATE .. _gin_pending_list_limit: https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-GIN-PENDING-LIST-LIMIT ``GistIndex`` ============= -.. class:: GistIndex(buffering=None, fillfactor=None, **options) +.. class:: GistIndex(*expressions, buffering=None, fillfactor=None, **options) Creates a `GiST index <https://www.postgresql.org/docs/current/gist.html>`_. These indexes are @@ -109,13 +129,18 @@ available from the ``django.contrib.postgres.indexes`` module. Provide an integer value from 10 to 100 to the fillfactor_ parameter to tune how packed the index pages will be. PostgreSQL's default is 90. + .. versionchanged:: 3.2 + + Positional argument ``*expressions`` was added in order to support + functional indexes. + .. _buffering build: https://www.postgresql.org/docs/current/gist-implementation.html#GIST-BUFFERING-BUILD .. _fillfactor: https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-STORAGE-PARAMETERS ``HashIndex`` ============= -.. class:: HashIndex(fillfactor=None, **options) +.. class:: HashIndex(*expressions, fillfactor=None, **options) Creates a hash index. @@ -127,12 +152,17 @@ available from the ``django.contrib.postgres.indexes`` module. Hash indexes have been available in PostgreSQL for a long time, but they suffer from a number of data integrity issues in older versions. + .. versionchanged:: 3.2 + + Positional argument ``*expressions`` was added in order to support + functional indexes. + .. _fillfactor: https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-STORAGE-PARAMETERS ``SpGistIndex`` =============== -.. class:: SpGistIndex(fillfactor=None, **options) +.. class:: SpGistIndex(*expressions, fillfactor=None, **options) Creates an `SP-GiST index <https://www.postgresql.org/docs/current/spgist.html>`_. @@ -140,4 +170,30 @@ available from the ``django.contrib.postgres.indexes`` module. Provide an integer value from 10 to 100 to the fillfactor_ parameter to tune how packed the index pages will be. PostgreSQL's default is 90. + .. versionchanged:: 3.2 + + Positional argument ``*expressions`` was added in order to support + functional indexes. + .. _fillfactor: https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-STORAGE-PARAMETERS + +``OpClass()`` expressions +========================= + +.. versionadded:: 3.2 + +.. class:: OpClass(expression, name) + + An ``OpClass()`` expression represents the ``expression`` with a custom + `operator class`_ that can be used to define functional indexes. To use it, + you need to add ``'django.contrib.postgres'`` in your + :setting:`INSTALLED_APPS`. Set the ``name`` parameter to the name of the + `operator class`_. + + For example:: + + Index(OpClass(Lower('username'), name='varchar_pattern_ops')) + + creates an index on ``Lower('username')`` using ``varchar_pattern_ops``. + + .. _operator class: https://www.postgresql.org/docs/current/indexes-opclass.html diff --git a/docs/ref/models/indexes.txt b/docs/ref/models/indexes.txt index 9dda45ad44..d2cf98e9e1 100644 --- a/docs/ref/models/indexes.txt +++ b/docs/ref/models/indexes.txt @@ -21,10 +21,55 @@ options`_. ``Index`` options ================= -.. class:: Index(fields=(), name=None, db_tablespace=None, opclasses=(), condition=None, include=None) +.. class:: Index(*expressions, fields=(), name=None, db_tablespace=None, opclasses=(), condition=None, include=None) Creates an index (B-Tree) in the database. +``expressions`` +--------------- + +.. attribute:: Index.expressions + +.. versionadded:: 3.2 + +Positional argument ``*expressions`` allows creating functional indexes on +expressions and database functions. + +For example:: + + Index(Lower('title').desc(), 'pub_date', name='lower_title_date_idx') + +creates an index on the lowercased value of the ``title`` field in descending +order and the ``pub_date`` field in the default ascending order. + +Another example:: + + Index(F('height') * F('weight'), Round('weight'), name='calc_idx') + +creates an index on the result of multiplying fields ``height`` and ``weight`` +and the ``weight`` rounded to the nearest integer. + +:attr:`Index.name` is required when using ``*expressions``. + +.. admonition:: Restrictions on Oracle + + Oracle requires functions referenced in an index to be marked as + ``DETERMINISTIC``. Django doesn't validate this but Oracle will error. This + means that functions such as + :class:`Random() <django.db.models.functions.Random>` aren't accepted. + +.. admonition:: Restrictions on PostgreSQL + + PostgreSQL requires functions and operators referenced in an index to be + marked as ``IMMUTABLE``. Django doesn't validate this but PostgreSQL will + error. This means that functions such as + :class:`Concat() <django.db.models.functions.Concat>` aren't accepted. + +.. admonition:: MySQL and MariaDB + + Functional indexes are ignored with MySQL < 8.0.13 and MariaDB as neither + supports them. + ``fields`` ---------- @@ -130,8 +175,8 @@ indexes records with more than 400 pages. .. admonition:: Oracle Oracle does not support partial indexes. Instead, partial indexes can be - emulated using functional indexes. Use a :doc:`migration - </topics/migrations>` to add the index using :class:`.RunSQL`. + emulated by using functional indexes together with + :class:`~django.db.models.expressions.Case` expressions. .. admonition:: MySQL and MariaDB diff --git a/docs/releases/3.2.txt b/docs/releases/3.2.txt index 8796419c62..57ab1baf34 100644 --- a/docs/releases/3.2.txt +++ b/docs/releases/3.2.txt @@ -95,6 +95,42 @@ or on a per-model basis:: In anticipation of the changing default, a system check will provide a warning if you do not have an explicit setting for :setting:`DEFAULT_AUTO_FIELD`. +.. _new_functional_indexes: + +Functional indexes +------------------ + +The new :attr:`*expressions <django.db.models.Index.expressions>` positional +argument of :class:`Index() <django.db.models.Index>` enables creating +functional indexes on expressions and database functions. For example:: + + from django.db import models + from django.db.models import F, Index, Value + from django.db.models.functions import Lower, Upper + + + class MyModel(models.Model): + first_name = models.CharField(max_length=255) + last_name = models.CharField(max_length=255) + height = models.IntegerField() + weight = models.IntegerField() + + class Meta: + indexes = [ + Index( + Lower('first_name'), + Upper('last_name').desc(), + name='first_last_name_idx', + ), + Index( + F('height') / (F('weight') + Value(5)), + name='calc_idx', + ), + ] + +Functional indexes are added to models using the +:attr:`Meta.indexes <django.db.models.Options.indexes>` option. + ``pymemcache`` support ---------------------- @@ -210,6 +246,10 @@ Minor features * Lookups for :class:`~django.contrib.postgres.fields.ArrayField` now allow (non-nested) arrays containing expressions as right-hand sides. +* The new :class:`OpClass() <django.contrib.postgres.indexes.OpClass>` + expression allows creating functional indexes on expressions with a custom + operator class. See :ref:`new_functional_indexes` for more details. + :mod:`django.contrib.redirects` ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ diff --git a/tests/backends/test_ddl_references.py b/tests/backends/test_ddl_references.py index d96ebcb57f..bd4036ee33 100644 --- a/tests/backends/test_ddl_references.py +++ b/tests/backends/test_ddl_references.py @@ -1,7 +1,13 @@ +from django.db import connection from django.db.backends.ddl_references import ( - Columns, ForeignKeyName, IndexName, Statement, Table, + Columns, Expressions, ForeignKeyName, IndexName, Statement, Table, ) -from django.test import SimpleTestCase +from django.db.models import ExpressionList, F +from django.db.models.functions import Upper +from django.db.models.indexes import IndexExpression +from django.test import SimpleTestCase, TransactionTestCase + +from .models import Person class TableTests(SimpleTestCase): @@ -181,3 +187,66 @@ class StatementTests(SimpleTestCase): reference = MockReference('reference', {}, {}) statement = Statement("%(reference)s - %(non_reference)s", reference=reference, non_reference='non_reference') self.assertEqual(str(statement), 'reference - non_reference') + + +class ExpressionsTests(TransactionTestCase): + available_apps = [] + + def setUp(self): + compiler = Person.objects.all().query.get_compiler(connection.alias) + self.editor = connection.schema_editor() + self.expressions = Expressions( + table=Person._meta.db_table, + expressions=ExpressionList( + IndexExpression(F('first_name')), + IndexExpression(F('last_name').desc()), + IndexExpression(Upper('last_name')), + ).resolve_expression(compiler.query), + compiler=compiler, + quote_value=self.editor.quote_value, + ) + + def test_references_table(self): + self.assertIs(self.expressions.references_table(Person._meta.db_table), True) + self.assertIs(self.expressions.references_table('other'), False) + + def test_references_column(self): + table = Person._meta.db_table + self.assertIs(self.expressions.references_column(table, 'first_name'), True) + self.assertIs(self.expressions.references_column(table, 'last_name'), True) + self.assertIs(self.expressions.references_column(table, 'other'), False) + + def test_rename_table_references(self): + table = Person._meta.db_table + self.expressions.rename_table_references(table, 'other') + self.assertIs(self.expressions.references_table(table), False) + self.assertIs(self.expressions.references_table('other'), True) + self.assertIn( + '%s.%s' % ( + self.editor.quote_name('other'), + self.editor.quote_name('first_name'), + ), + str(self.expressions), + ) + + def test_rename_column_references(self): + table = Person._meta.db_table + self.expressions.rename_column_references(table, 'first_name', 'other') + self.assertIs(self.expressions.references_column(table, 'other'), True) + self.assertIs(self.expressions.references_column(table, 'first_name'), False) + self.assertIn( + '%s.%s' % (self.editor.quote_name(table), self.editor.quote_name('other')), + str(self.expressions), + ) + + def test_str(self): + table_name = self.editor.quote_name(Person._meta.db_table) + expected_str = '%s.%s, %s.%s DESC, (UPPER(%s.%s))' % ( + table_name, + self.editor.quote_name('first_name'), + table_name, + self.editor.quote_name('last_name'), + table_name, + self.editor.quote_name('last_name'), + ) + self.assertEqual(str(self.expressions), expected_str) diff --git a/tests/indexes/tests.py b/tests/indexes/tests.py index 6d01e3b52f..ae68113c75 100644 --- a/tests/indexes/tests.py +++ b/tests/indexes/tests.py @@ -3,6 +3,7 @@ from unittest import skipUnless from django.db import connection from django.db.models import CASCADE, ForeignKey, Index, Q +from django.db.models.functions import Lower from django.test import ( TestCase, TransactionTestCase, skipIfDBFeature, skipUnlessDBFeature, ) @@ -452,6 +453,40 @@ class PartialIndexTests(TransactionTestCase): )) editor.remove_index(index=index, model=Article) + @skipUnlessDBFeature('supports_expression_indexes') + def test_partial_func_index(self): + index_name = 'partial_func_idx' + index = Index( + Lower('headline').desc(), + name=index_name, + condition=Q(pub_date__isnull=False), + ) + with connection.schema_editor() as editor: + editor.add_index(index=index, model=Article) + sql = index.create_sql(Article, schema_editor=editor) + table = Article._meta.db_table + self.assertIs(sql.references_column(table, 'headline'), True) + sql = str(sql) + self.assertIn('LOWER(%s)' % editor.quote_name('headline'), sql) + self.assertIn( + 'WHERE %s IS NOT NULL' % editor.quote_name('pub_date'), + sql, + ) + self.assertGreater(sql.find('WHERE'), sql.find('LOWER')) + with connection.cursor() as cursor: + constraints = connection.introspection.get_constraints( + cursor=cursor, table_name=table, + ) + self.assertIn(index_name, constraints) + if connection.features.supports_index_column_ordering: + self.assertEqual(constraints[index_name]['orders'], ['DESC']) + with connection.schema_editor() as editor: + editor.remove_index(Article, index) + with connection.cursor() as cursor: + self.assertNotIn(index_name, connection.introspection.get_constraints( + cursor=cursor, table_name=table, + )) + @skipUnlessDBFeature('supports_covering_indexes') class CoveringIndexTests(TransactionTestCase): @@ -520,6 +555,31 @@ class CoveringIndexTests(TransactionTestCase): cursor=cursor, table_name=Article._meta.db_table, )) + def test_covering_func_index(self): + index_name = 'covering_func_headline_idx' + index = Index(Lower('headline'), name=index_name, include=['pub_date']) + with connection.schema_editor() as editor: + editor.add_index(index=index, model=Article) + sql = index.create_sql(Article, schema_editor=editor) + table = Article._meta.db_table + self.assertIs(sql.references_column(table, 'headline'), True) + sql = str(sql) + self.assertIn('LOWER(%s)' % editor.quote_name('headline'), sql) + self.assertIn('INCLUDE (%s)' % editor.quote_name('pub_date'), sql) + self.assertGreater(sql.find('INCLUDE'), sql.find('LOWER')) + with connection.cursor() as cursor: + constraints = connection.introspection.get_constraints( + cursor=cursor, table_name=table, + ) + self.assertIn(index_name, constraints) + self.assertIn('pub_date', constraints[index_name]['columns']) + with connection.schema_editor() as editor: + editor.remove_index(Article, index) + with connection.cursor() as cursor: + self.assertNotIn(index_name, connection.introspection.get_constraints( + cursor=cursor, table_name=table, + )) + @skipIfDBFeature('supports_covering_indexes') class CoveringIndexIgnoredTests(TransactionTestCase): diff --git a/tests/invalid_models_tests/test_models.py b/tests/invalid_models_tests/test_models.py index d9993c00cd..3203c26a2e 100644 --- a/tests/invalid_models_tests/test_models.py +++ b/tests/invalid_models_tests/test_models.py @@ -495,6 +495,36 @@ class IndexesTests(TestCase): self.assertEqual(Model.check(databases=self.databases), []) + def test_func_index(self): + class Model(models.Model): + name = models.CharField(max_length=10) + + class Meta: + indexes = [models.Index(Lower('name'), name='index_lower_name')] + + warn = Warning( + '%s does not support indexes on expressions.' + % connection.display_name, + hint=( + "An index won't be created. Silence this warning if you don't " + "care about it." + ), + obj=Model, + id='models.W043', + ) + expected = [] if connection.features.supports_expression_indexes else [warn] + self.assertEqual(Model.check(databases=self.databases), expected) + + def test_func_index_required_db_features(self): + class Model(models.Model): + name = models.CharField(max_length=10) + + class Meta: + indexes = [models.Index(Lower('name'), name='index_lower_name')] + required_db_features = {'supports_expression_indexes'} + + self.assertEqual(Model.check(databases=self.databases), []) + @isolate_apps('invalid_models_tests') class FieldNamesTests(TestCase): diff --git a/tests/migrations/test_base.py b/tests/migrations/test_base.py index 9adc0d5264..6f8081a462 100644 --- a/tests/migrations/test_base.py +++ b/tests/migrations/test_base.py @@ -73,6 +73,20 @@ class MigrationTestBase(TransactionTestCase): def assertIndexNotExists(self, table, columns): return self.assertIndexExists(table, columns, False) + def assertIndexNameExists(self, table, index, using='default'): + with connections[using].cursor() as cursor: + self.assertIn( + index, + connection.introspection.get_constraints(cursor, table), + ) + + def assertIndexNameNotExists(self, table, index, using='default'): + with connections[using].cursor() as cursor: + self.assertNotIn( + index, + connection.introspection.get_constraints(cursor, table), + ) + def assertConstraintExists(self, table, name, value=True, using='default'): with connections[using].cursor() as cursor: constraints = connections[using].introspection.get_constraints(cursor, table).items() @@ -194,6 +208,7 @@ class OperationTestBase(MigrationTestBase): multicol_index=False, related_model=False, mti_model=False, proxy_model=False, manager_model=False, unique_together=False, options=False, db_table=None, index_together=False, constraints=None, + indexes=None, ): """Creates a test model state and database table.""" # Make the "current" state. @@ -225,6 +240,9 @@ class OperationTestBase(MigrationTestBase): 'Pony', models.Index(fields=['pink', 'weight'], name='pony_test_idx'), )) + if indexes: + for index in indexes: + operations.append(migrations.AddIndex('Pony', index)) if constraints: for constraint in constraints: operations.append(migrations.AddConstraint('Pony', constraint)) diff --git a/tests/migrations/test_operations.py b/tests/migrations/test_operations.py index 38ad6a2d18..897808f75b 100644 --- a/tests/migrations/test_operations.py +++ b/tests/migrations/test_operations.py @@ -5,6 +5,7 @@ from django.db import ( from django.db.migrations.migration import Migration from django.db.migrations.operations.fields import FieldOperation from django.db.migrations.state import ModelState, ProjectState +from django.db.models.functions import Abs from django.db.transaction import atomic from django.test import SimpleTestCase, override_settings, skipUnlessDBFeature @@ -1939,6 +1940,76 @@ class OperationTests(OperationTestBase): new_model = new_state.apps.get_model('test_rminsf', 'Pony') self.assertIsNot(old_model, new_model) + @skipUnlessDBFeature('supports_expression_indexes') + def test_add_func_index(self): + app_label = 'test_addfuncin' + index_name = f'{app_label}_pony_abs_idx' + table_name = f'{app_label}_pony' + project_state = self.set_up_test_model(app_label) + index = models.Index(Abs('weight'), name=index_name) + operation = migrations.AddIndex('Pony', index) + self.assertEqual( + operation.describe(), + 'Create index test_addfuncin_pony_abs_idx on Abs(F(weight)) on model Pony', + ) + self.assertEqual( + operation.migration_name_fragment, + 'pony_test_addfuncin_pony_abs_idx', + ) + new_state = project_state.clone() + operation.state_forwards(app_label, new_state) + self.assertEqual(len(new_state.models[app_label, 'pony'].options['indexes']), 1) + self.assertIndexNameNotExists(table_name, index_name) + # Add index. + with connection.schema_editor() as editor: + operation.database_forwards(app_label, editor, project_state, new_state) + self.assertIndexNameExists(table_name, index_name) + # Reversal. + with connection.schema_editor() as editor: + operation.database_backwards(app_label, editor, new_state, project_state) + self.assertIndexNameNotExists(table_name, index_name) + # Deconstruction. + definition = operation.deconstruct() + self.assertEqual(definition[0], 'AddIndex') + self.assertEqual(definition[1], []) + self.assertEqual(definition[2], {'model_name': 'Pony', 'index': index}) + + @skipUnlessDBFeature('supports_expression_indexes') + def test_remove_func_index(self): + app_label = 'test_rmfuncin' + index_name = f'{app_label}_pony_abs_idx' + table_name = f'{app_label}_pony' + project_state = self.set_up_test_model(app_label, indexes=[ + models.Index(Abs('weight'), name=index_name), + ]) + self.assertTableExists(table_name) + self.assertIndexNameExists(table_name, index_name) + operation = migrations.RemoveIndex('Pony', index_name) + self.assertEqual( + operation.describe(), + 'Remove index test_rmfuncin_pony_abs_idx from Pony', + ) + self.assertEqual( + operation.migration_name_fragment, + 'remove_pony_test_rmfuncin_pony_abs_idx', + ) + new_state = project_state.clone() + operation.state_forwards(app_label, new_state) + self.assertEqual(len(new_state.models[app_label, 'pony'].options['indexes']), 0) + # Remove index. + with connection.schema_editor() as editor: + operation.database_forwards(app_label, editor, project_state, new_state) + self.assertIndexNameNotExists(table_name, index_name) + # Reversal. + with connection.schema_editor() as editor: + operation.database_backwards(app_label, editor, new_state, project_state) + self.assertIndexNameExists(table_name, index_name) + # Deconstruction. + definition = operation.deconstruct() + self.assertEqual(definition[0], 'RemoveIndex') + self.assertEqual(definition[1], []) + self.assertEqual(definition[2], {'model_name': 'Pony', 'name': index_name}) + def test_alter_field_with_index(self): """ Test AlterField operation with an index to ensure indexes created via diff --git a/tests/model_indexes/tests.py b/tests/model_indexes/tests.py index 1fe283340e..ab231edd5e 100644 --- a/tests/model_indexes/tests.py +++ b/tests/model_indexes/tests.py @@ -2,6 +2,7 @@ from unittest import mock from django.conf import settings from django.db import connection, models +from django.db.models.functions import Lower, Upper from django.test import SimpleTestCase, TestCase, skipUnlessDBFeature from django.test.utils import isolate_apps @@ -27,6 +28,7 @@ class SimpleIndexesTests(SimpleTestCase): name='opclasses_idx', opclasses=['varchar_pattern_ops', 'text_pattern_ops'], ) + func_index = models.Index(Lower('title'), name='book_func_idx') self.assertEqual(repr(index), "<Index: fields='title'>") self.assertEqual(repr(multi_col_index), "<Index: fields='title, author'>") self.assertEqual(repr(partial_index), "<Index: fields='title' condition=(AND: ('pages__gt', 400))>") @@ -39,6 +41,7 @@ class SimpleIndexesTests(SimpleTestCase): "<Index: fields='headline, body' " "opclasses='varchar_pattern_ops, text_pattern_ops'>", ) + self.assertEqual(repr(func_index), "<Index: expressions='Lower(F(title))'>") def test_eq(self): index = models.Index(fields=['title']) @@ -51,6 +54,14 @@ class SimpleIndexesTests(SimpleTestCase): self.assertEqual(index, mock.ANY) self.assertNotEqual(index, another_index) + def test_eq_func(self): + index = models.Index(Lower('title'), models.F('author'), name='book_func_idx') + same_index = models.Index(Lower('title'), 'author', name='book_func_idx') + another_index = models.Index(Lower('title'), name='book_func_idx') + self.assertEqual(index, same_index) + self.assertEqual(index, mock.ANY) + self.assertNotEqual(index, another_index) + def test_index_fields_type(self): with self.assertRaisesMessage(ValueError, 'Index.fields must be a list or tuple.'): models.Index(fields='title') @@ -63,11 +74,16 @@ class SimpleIndexesTests(SimpleTestCase): def test_fields_tuple(self): self.assertEqual(models.Index(fields=('title',)).fields, ['title']) - def test_raises_error_without_field(self): - msg = 'At least one field is required to define an index.' + def test_requires_field_or_expression(self): + msg = 'At least one field or expression is required to define an index.' with self.assertRaisesMessage(ValueError, msg): models.Index() + def test_expressions_and_fields_mutually_exclusive(self): + msg = "Index.fields and expressions are mutually exclusive." + with self.assertRaisesMessage(ValueError, msg): + models.Index(Upper('foo'), fields=['field']) + def test_opclasses_requires_index_name(self): with self.assertRaisesMessage(ValueError, 'An index must be named to use opclasses.'): models.Index(opclasses=['jsonb_path_ops']) @@ -85,6 +101,23 @@ class SimpleIndexesTests(SimpleTestCase): with self.assertRaisesMessage(ValueError, 'An index must be named to use condition.'): models.Index(condition=models.Q(pages__gt=400)) + def test_expressions_requires_index_name(self): + msg = 'An index must be named to use expressions.' + with self.assertRaisesMessage(ValueError, msg): + models.Index(Lower('field')) + + def test_expressions_with_opclasses(self): + msg = ( + 'Index.opclasses cannot be used with expressions. Use ' + 'django.contrib.postgres.indexes.OpClass() instead.' + ) + with self.assertRaisesMessage(ValueError, msg): + models.Index( + Lower('field'), + name='test_func_opclass', + opclasses=['jsonb_path_ops'], + ) + def test_condition_must_be_q(self): with self.assertRaisesMessage(ValueError, 'Index.condition must be a Q instance.'): models.Index(condition='invalid', name='long_book_idx') @@ -181,12 +214,25 @@ class SimpleIndexesTests(SimpleTestCase): }, ) + def test_deconstruct_with_expressions(self): + index = models.Index(Upper('title'), name='book_func_idx') + path, args, kwargs = index.deconstruct() + self.assertEqual(path, 'django.db.models.Index') + self.assertEqual(args, (Upper('title'),)) + self.assertEqual(kwargs, {'name': 'book_func_idx'}) + def test_clone(self): index = models.Index(fields=['title']) new_index = index.clone() self.assertIsNot(index, new_index) self.assertEqual(index.fields, new_index.fields) + def test_clone_with_expressions(self): + index = models.Index(Upper('title'), name='book_func_idx') + new_index = index.clone() + self.assertIsNot(index, new_index) + self.assertEqual(index.expressions, new_index.expressions) + def test_name_set(self): index_names = [index.name for index in Book._meta.indexes] self.assertCountEqual( @@ -248,3 +294,29 @@ class IndexesTests(TestCase): # db_tablespace. index = models.Index(fields=['shortcut']) self.assertIn('"idx_tbls"', str(index.create_sql(Book, editor)).lower()) + + @skipUnlessDBFeature('supports_tablespaces') + def test_func_with_tablespace(self): + # Functional index with db_tablespace attribute. + index = models.Index( + Lower('shortcut').desc(), + name='functional_tbls', + db_tablespace='idx_tbls2', + ) + with connection.schema_editor() as editor: + sql = str(index.create_sql(Book, editor)) + self.assertIn(editor.quote_name('idx_tbls2'), sql) + # Functional index without db_tablespace attribute. + index = models.Index(Lower('shortcut').desc(), name='functional_no_tbls') + with connection.schema_editor() as editor: + sql = str(index.create_sql(Book, editor)) + # The DEFAULT_INDEX_TABLESPACE setting can't be tested because it's + # evaluated when the model class is defined. As a consequence, + # @override_settings doesn't work. + if settings.DEFAULT_INDEX_TABLESPACE: + self.assertIn( + editor.quote_name(settings.DEFAULT_INDEX_TABLESPACE), + sql, + ) + else: + self.assertNotIn('TABLESPACE', sql) diff --git a/tests/postgres_tests/fields.py b/tests/postgres_tests/fields.py index a36c10c750..b1bb6668d6 100644 --- a/tests/postgres_tests/fields.py +++ b/tests/postgres_tests/fields.py @@ -12,7 +12,7 @@ try: CITextField, DateRangeField, DateTimeRangeField, DecimalRangeField, HStoreField, IntegerRangeField, ) - from django.contrib.postgres.search import SearchVectorField + from django.contrib.postgres.search import SearchVector, SearchVectorField except ImportError: class DummyArrayField(models.Field): def __init__(self, base_field, size=None, **kwargs): @@ -36,6 +36,7 @@ except ImportError: DecimalRangeField = models.Field HStoreField = models.Field IntegerRangeField = models.Field + SearchVector = models.Expression SearchVectorField = models.Field diff --git a/tests/postgres_tests/test_indexes.py b/tests/postgres_tests/test_indexes.py index b9888f4843..49646feb97 100644 --- a/tests/postgres_tests/test_indexes.py +++ b/tests/postgres_tests/test_indexes.py @@ -1,17 +1,18 @@ from unittest import mock from django.contrib.postgres.indexes import ( - BloomIndex, BrinIndex, BTreeIndex, GinIndex, GistIndex, HashIndex, + BloomIndex, BrinIndex, BTreeIndex, GinIndex, GistIndex, HashIndex, OpClass, SpGistIndex, ) from django.db import NotSupportedError, connection -from django.db.models import CharField, Q -from django.db.models.functions import Length +from django.db.models import CharField, F, Index, Q +from django.db.models.functions import Cast, Collate, Length, Lower from django.test import skipUnlessDBFeature -from django.test.utils import register_lookup +from django.test.utils import modify_settings, register_lookup from . import PostgreSQLSimpleTestCase, PostgreSQLTestCase -from .models import CharFieldModel, IntegerArrayModel, Scene +from .fields import SearchVector, SearchVectorField +from .models import CharFieldModel, IntegerArrayModel, Scene, TextFieldModel class IndexTestMixin: @@ -28,6 +29,17 @@ class IndexTestMixin: self.assertEqual(args, ()) self.assertEqual(kwargs, {'fields': ['title'], 'name': 'test_title_%s' % self.index_class.suffix}) + def test_deconstruction_with_expressions_no_customization(self): + name = f'test_title_{self.index_class.suffix}' + index = self.index_class(Lower('title'), name=name) + path, args, kwargs = index.deconstruct() + self.assertEqual( + path, + f'django.contrib.postgres.indexes.{self.index_class.__name__}', + ) + self.assertEqual(args, (Lower('title'),)) + self.assertEqual(kwargs, {'name': name}) + class BloomIndexTests(IndexTestMixin, PostgreSQLSimpleTestCase): index_class = BloomIndex @@ -181,7 +193,14 @@ class SpGistIndexTests(IndexTestMixin, PostgreSQLSimpleTestCase): self.assertEqual(kwargs, {'fields': ['title'], 'name': 'test_title_spgist', 'fillfactor': 80}) +@modify_settings(INSTALLED_APPS={'append': 'django.contrib.postgres'}) class SchemaTests(PostgreSQLTestCase): + get_opclass_query = ''' + SELECT opcname, c.relname FROM pg_opclass AS oc + JOIN pg_index as i on oc.oid = ANY(i.indclass) + JOIN pg_class as c on c.oid = i.indexrelid + WHERE c.relname = %s + ''' def get_constraints(self, table): """ @@ -260,6 +279,37 @@ class SchemaTests(PostgreSQLTestCase): editor.remove_index(IntegerArrayModel, index) self.assertNotIn(index_name, self.get_constraints(IntegerArrayModel._meta.db_table)) + def test_trigram_op_class_gin_index(self): + index_name = 'trigram_op_class_gin' + index = GinIndex(OpClass(F('scene'), name='gin_trgm_ops'), name=index_name) + with connection.schema_editor() as editor: + editor.add_index(Scene, index) + with editor.connection.cursor() as cursor: + cursor.execute(self.get_opclass_query, [index_name]) + self.assertCountEqual(cursor.fetchall(), [('gin_trgm_ops', index_name)]) + constraints = self.get_constraints(Scene._meta.db_table) + self.assertIn(index_name, constraints) + self.assertIn(constraints[index_name]['type'], GinIndex.suffix) + with connection.schema_editor() as editor: + editor.remove_index(Scene, index) + self.assertNotIn(index_name, self.get_constraints(Scene._meta.db_table)) + + def test_cast_search_vector_gin_index(self): + index_name = 'cast_search_vector_gin' + index = GinIndex(Cast('field', SearchVectorField()), name=index_name) + with connection.schema_editor() as editor: + editor.add_index(TextFieldModel, index) + sql = index.create_sql(TextFieldModel, editor) + table = TextFieldModel._meta.db_table + constraints = self.get_constraints(table) + self.assertIn(index_name, constraints) + self.assertIn(constraints[index_name]['type'], GinIndex.suffix) + self.assertIs(sql.references_column(table, 'field'), True) + self.assertIn('::tsvector', str(sql)) + with connection.schema_editor() as editor: + editor.remove_index(TextFieldModel, index) + self.assertNotIn(index_name, self.get_constraints(table)) + def test_bloom_index(self): index_name = 'char_field_model_field_bloom' index = BloomIndex(fields=['field'], name=index_name) @@ -400,6 +450,28 @@ class SchemaTests(PostgreSQLTestCase): editor.add_index(Scene, index) self.assertNotIn(index_name, self.get_constraints(Scene._meta.db_table)) + def test_tsvector_op_class_gist_index(self): + index_name = 'tsvector_op_class_gist' + index = GistIndex( + OpClass( + SearchVector('scene', 'setting', config='english'), + name='tsvector_ops', + ), + name=index_name, + ) + with connection.schema_editor() as editor: + editor.add_index(Scene, index) + sql = index.create_sql(Scene, editor) + table = Scene._meta.db_table + constraints = self.get_constraints(table) + self.assertIn(index_name, constraints) + self.assertIn(constraints[index_name]['type'], GistIndex.suffix) + self.assertIs(sql.references_column(table, 'scene'), True) + self.assertIs(sql.references_column(table, 'setting'), True) + with connection.schema_editor() as editor: + editor.remove_index(Scene, index) + self.assertNotIn(index_name, self.get_constraints(table)) + def test_hash_index(self): # Ensure the table is there and doesn't have an index. self.assertNotIn('field', self.get_constraints(CharFieldModel._meta.db_table)) @@ -455,3 +527,83 @@ class SchemaTests(PostgreSQLTestCase): with connection.schema_editor() as editor: editor.remove_index(CharFieldModel, index) self.assertNotIn(index_name, self.get_constraints(CharFieldModel._meta.db_table)) + + def test_op_class(self): + index_name = 'test_op_class' + index = Index( + OpClass(Lower('field'), name='text_pattern_ops'), + name=index_name, + ) + with connection.schema_editor() as editor: + editor.add_index(TextFieldModel, index) + with editor.connection.cursor() as cursor: + cursor.execute(self.get_opclass_query, [index_name]) + self.assertCountEqual(cursor.fetchall(), [('text_pattern_ops', index_name)]) + + def test_op_class_descending_collation(self): + collation = connection.features.test_collations.get('non_default') + if not collation: + self.skipTest( + 'This backend does not support case-insensitive collations.' + ) + index_name = 'test_op_class_descending_collation' + index = Index( + Collate( + OpClass(Lower('field'), name='text_pattern_ops').desc(nulls_last=True), + collation=collation, + ), + name=index_name, + ) + with connection.schema_editor() as editor: + editor.add_index(TextFieldModel, index) + self.assertIn( + 'COLLATE %s' % editor.quote_name(collation), + str(index.create_sql(TextFieldModel, editor)), + ) + with editor.connection.cursor() as cursor: + cursor.execute(self.get_opclass_query, [index_name]) + self.assertCountEqual(cursor.fetchall(), [('text_pattern_ops', index_name)]) + table = TextFieldModel._meta.db_table + constraints = self.get_constraints(table) + self.assertIn(index_name, constraints) + self.assertEqual(constraints[index_name]['orders'], ['DESC']) + with connection.schema_editor() as editor: + editor.remove_index(TextFieldModel, index) + self.assertNotIn(index_name, self.get_constraints(table)) + + def test_op_class_descending_partial(self): + index_name = 'test_op_class_descending_partial' + index = Index( + OpClass(Lower('field'), name='text_pattern_ops').desc(), + name=index_name, + condition=Q(field__contains='China'), + ) + with connection.schema_editor() as editor: + editor.add_index(TextFieldModel, index) + with editor.connection.cursor() as cursor: + cursor.execute(self.get_opclass_query, [index_name]) + self.assertCountEqual(cursor.fetchall(), [('text_pattern_ops', index_name)]) + constraints = self.get_constraints(TextFieldModel._meta.db_table) + self.assertIn(index_name, constraints) + self.assertEqual(constraints[index_name]['orders'], ['DESC']) + + def test_op_class_descending_partial_tablespace(self): + index_name = 'test_op_class_descending_partial_tablespace' + index = Index( + OpClass(Lower('field').desc(), name='text_pattern_ops'), + name=index_name, + condition=Q(field__contains='China'), + db_tablespace='pg_default', + ) + with connection.schema_editor() as editor: + editor.add_index(TextFieldModel, index) + self.assertIn( + 'TABLESPACE "pg_default" ', + str(index.create_sql(TextFieldModel, editor)) + ) + with editor.connection.cursor() as cursor: + cursor.execute(self.get_opclass_query, [index_name]) + self.assertCountEqual(cursor.fetchall(), [('text_pattern_ops', index_name)]) + constraints = self.get_constraints(TextFieldModel._meta.db_table) + self.assertIn(index_name, constraints) + self.assertEqual(constraints[index_name]['orders'], ['DESC']) diff --git a/tests/schema/tests.py b/tests/schema/tests.py index 7740065d31..b994c252d9 100644 --- a/tests/schema/tests.py +++ b/tests/schema/tests.py @@ -4,6 +4,7 @@ import unittest from copy import copy from unittest import mock +from django.core.exceptions import FieldError from django.core.management.color import no_style from django.db import ( DatabaseError, DataError, IntegrityError, OperationalError, connection, @@ -11,15 +12,21 @@ from django.db import ( from django.db.models import ( CASCADE, PROTECT, AutoField, BigAutoField, BigIntegerField, BinaryField, BooleanField, CharField, CheckConstraint, DateField, DateTimeField, - ForeignKey, ForeignObject, Index, IntegerField, ManyToManyField, Model, - OneToOneField, PositiveIntegerField, Q, SlugField, SmallAutoField, - SmallIntegerField, TextField, TimeField, UniqueConstraint, UUIDField, + DecimalField, F, FloatField, ForeignKey, ForeignObject, Index, + IntegerField, JSONField, ManyToManyField, Model, OneToOneField, OrderBy, + PositiveIntegerField, Q, SlugField, SmallAutoField, SmallIntegerField, + TextField, TimeField, UniqueConstraint, UUIDField, Value, ) +from django.db.models.fields.json import KeyTextTransform +from django.db.models.functions import Abs, Cast, Collate, Lower, Random, Upper +from django.db.models.indexes import IndexExpression from django.db.transaction import TransactionManagementError, atomic from django.test import ( TransactionTestCase, skipIfDBFeature, skipUnlessDBFeature, ) -from django.test.utils import CaptureQueriesContext, isolate_apps +from django.test.utils import ( + CaptureQueriesContext, isolate_apps, register_lookup, +) from django.utils import timezone from .fields import ( @@ -2481,6 +2488,366 @@ class SchemaTests(TransactionTestCase): assertion = self.assertIn if connection.features.supports_index_on_text_field else self.assertNotIn assertion('text_field', self.get_indexes(AuthorTextFieldWithIndex._meta.db_table)) + def _index_expressions_wrappers(self): + index_expression = IndexExpression() + index_expression.set_wrapper_classes(connection) + return ', '.join([ + wrapper_cls.__qualname__ for wrapper_cls in index_expression.wrapper_classes + ]) + + @skipUnlessDBFeature('supports_expression_indexes') + def test_func_index_multiple_wrapper_references(self): + index = Index(OrderBy(F('name').desc(), descending=True), name='name') + msg = ( + "Multiple references to %s can't be used in an indexed expression." + % self._index_expressions_wrappers() + ) + with connection.schema_editor() as editor: + with self.assertRaisesMessage(ValueError, msg): + editor.add_index(Author, index) + + @skipUnlessDBFeature('supports_expression_indexes') + def test_func_index_invalid_topmost_expressions(self): + index = Index(Upper(F('name').desc()), name='name') + msg = ( + '%s must be topmost expressions in an indexed expression.' + % self._index_expressions_wrappers() + ) + with connection.schema_editor() as editor: + with self.assertRaisesMessage(ValueError, msg): + editor.add_index(Author, index) + + @skipUnlessDBFeature('supports_expression_indexes') + def test_func_index(self): + with connection.schema_editor() as editor: + editor.create_model(Author) + index = Index(Lower('name').desc(), name='func_lower_idx') + # Add index. + with connection.schema_editor() as editor: + editor.add_index(Author, index) + sql = index.create_sql(Author, editor) + table = Author._meta.db_table + if connection.features.supports_index_column_ordering: + self.assertIndexOrder(table, index.name, ['DESC']) + # SQL contains a database function. + self.assertIs(sql.references_column(table, 'name'), True) + self.assertIn('LOWER(%s)' % editor.quote_name('name'), str(sql)) + # Remove index. + with connection.schema_editor() as editor: + editor.remove_index(Author, index) + self.assertNotIn(index.name, self.get_constraints(table)) + + @skipUnlessDBFeature('supports_expression_indexes') + def test_func_index_f(self): + with connection.schema_editor() as editor: + editor.create_model(Tag) + index = Index('slug', F('title').desc(), name='func_f_idx') + # Add index. + with connection.schema_editor() as editor: + editor.add_index(Tag, index) + sql = index.create_sql(Tag, editor) + table = Tag._meta.db_table + self.assertIn(index.name, self.get_constraints(table)) + if connection.features.supports_index_column_ordering: + self.assertIndexOrder(Tag._meta.db_table, index.name, ['ASC', 'DESC']) + # SQL contains columns. + self.assertIs(sql.references_column(table, 'slug'), True) + self.assertIs(sql.references_column(table, 'title'), True) + # Remove index. + with connection.schema_editor() as editor: + editor.remove_index(Tag, index) + self.assertNotIn(index.name, self.get_constraints(table)) + + @skipUnlessDBFeature('supports_expression_indexes') + def test_func_index_lookups(self): + with connection.schema_editor() as editor: + editor.create_model(Author) + with register_lookup(CharField, Lower), register_lookup(IntegerField, Abs): + index = Index( + F('name__lower'), + F('weight__abs'), + name='func_lower_abs_lookup_idx', + ) + # Add index. + with connection.schema_editor() as editor: + editor.add_index(Author, index) + sql = index.create_sql(Author, editor) + table = Author._meta.db_table + self.assertIn(index.name, self.get_constraints(table)) + # SQL contains columns. + self.assertIs(sql.references_column(table, 'name'), True) + self.assertIs(sql.references_column(table, 'weight'), True) + # Remove index. + with connection.schema_editor() as editor: + editor.remove_index(Author, index) + self.assertNotIn(index.name, self.get_constraints(table)) + + @skipUnlessDBFeature('supports_expression_indexes') + def test_composite_func_index(self): + with connection.schema_editor() as editor: + editor.create_model(Author) + index = Index(Lower('name'), Upper('name'), name='func_lower_upper_idx') + # Add index. + with connection.schema_editor() as editor: + editor.add_index(Author, index) + sql = index.create_sql(Author, editor) + table = Author._meta.db_table + self.assertIn(index.name, self.get_constraints(table)) + # SQL contains database functions. + self.assertIs(sql.references_column(table, 'name'), True) + sql = str(sql) + self.assertIn('LOWER(%s)' % editor.quote_name('name'), sql) + self.assertIn('UPPER(%s)' % editor.quote_name('name'), sql) + self.assertLess(sql.index('LOWER'), sql.index('UPPER')) + # Remove index. + with connection.schema_editor() as editor: + editor.remove_index(Author, index) + self.assertNotIn(index.name, self.get_constraints(table)) + + @skipUnlessDBFeature('supports_expression_indexes') + def test_composite_func_index_field_and_expression(self): + with connection.schema_editor() as editor: + editor.create_model(Author) + editor.create_model(Book) + index = Index( + F('author').desc(), + Lower('title').asc(), + 'pub_date', + name='func_f_lower_field_idx', + ) + # Add index. + with connection.schema_editor() as editor: + editor.add_index(Book, index) + sql = index.create_sql(Book, editor) + table = Book._meta.db_table + constraints = self.get_constraints(table) + if connection.features.supports_index_column_ordering: + self.assertIndexOrder(table, index.name, ['DESC', 'ASC', 'ASC']) + self.assertEqual(len(constraints[index.name]['columns']), 3) + self.assertEqual(constraints[index.name]['columns'][2], 'pub_date') + # SQL contains database functions and columns. + self.assertIs(sql.references_column(table, 'author_id'), True) + self.assertIs(sql.references_column(table, 'title'), True) + self.assertIs(sql.references_column(table, 'pub_date'), True) + self.assertIn('LOWER(%s)' % editor.quote_name('title'), str(sql)) + # Remove index. + with connection.schema_editor() as editor: + editor.remove_index(Book, index) + self.assertNotIn(index.name, self.get_constraints(table)) + + @skipUnlessDBFeature('supports_expression_indexes') + @isolate_apps('schema') + def test_func_index_f_decimalfield(self): + class Node(Model): + value = DecimalField(max_digits=5, decimal_places=2) + + class Meta: + app_label = 'schema' + + with connection.schema_editor() as editor: + editor.create_model(Node) + index = Index(F('value'), name='func_f_decimalfield_idx') + # Add index. + with connection.schema_editor() as editor: + editor.add_index(Node, index) + sql = index.create_sql(Node, editor) + table = Node._meta.db_table + self.assertIn(index.name, self.get_constraints(table)) + self.assertIs(sql.references_column(table, 'value'), True) + # SQL doesn't contain casting. + self.assertNotIn('CAST', str(sql)) + # Remove index. + with connection.schema_editor() as editor: + editor.remove_index(Node, index) + self.assertNotIn(index.name, self.get_constraints(table)) + + @skipUnlessDBFeature('supports_expression_indexes') + def test_func_index_cast(self): + with connection.schema_editor() as editor: + editor.create_model(Author) + index = Index(Cast('weight', FloatField()), name='func_cast_idx') + # Add index. + with connection.schema_editor() as editor: + editor.add_index(Author, index) + sql = index.create_sql(Author, editor) + table = Author._meta.db_table + self.assertIn(index.name, self.get_constraints(table)) + self.assertIs(sql.references_column(table, 'weight'), True) + # Remove index. + with connection.schema_editor() as editor: + editor.remove_index(Author, index) + self.assertNotIn(index.name, self.get_constraints(table)) + + @skipUnlessDBFeature('supports_expression_indexes') + def test_func_index_collate(self): + collation = connection.features.test_collations.get('non_default') + if not collation: + self.skipTest( + 'This backend does not support case-insensitive collations.' + ) + with connection.schema_editor() as editor: + editor.create_model(Author) + editor.create_model(BookWithSlug) + index = Index( + Collate(F('title'), collation=collation).desc(), + Collate('slug', collation=collation), + name='func_collate_idx', + ) + # Add index. + with connection.schema_editor() as editor: + editor.add_index(BookWithSlug, index) + sql = index.create_sql(BookWithSlug, editor) + table = Book._meta.db_table + self.assertIn(index.name, self.get_constraints(table)) + if connection.features.supports_index_column_ordering: + self.assertIndexOrder(table, index.name, ['DESC', 'ASC']) + # SQL contains columns and a collation. + self.assertIs(sql.references_column(table, 'title'), True) + self.assertIs(sql.references_column(table, 'slug'), True) + self.assertIn('COLLATE %s' % editor.quote_name(collation), str(sql)) + # Remove index. + with connection.schema_editor() as editor: + editor.remove_index(Book, index) + self.assertNotIn(index.name, self.get_constraints(table)) + + @skipUnlessDBFeature('supports_expression_indexes') + @skipIfDBFeature('collate_as_index_expression') + def test_func_index_collate_f_ordered(self): + collation = connection.features.test_collations.get('non_default') + if not collation: + self.skipTest( + 'This backend does not support case-insensitive collations.' + ) + with connection.schema_editor() as editor: + editor.create_model(Author) + index = Index( + Collate(F('name').desc(), collation=collation), + name='func_collate_f_desc_idx', + ) + # Add index. + with connection.schema_editor() as editor: + editor.add_index(Author, index) + sql = index.create_sql(Author, editor) + table = Author._meta.db_table + self.assertIn(index.name, self.get_constraints(table)) + if connection.features.supports_index_column_ordering: + self.assertIndexOrder(table, index.name, ['DESC']) + # SQL contains columns and a collation. + self.assertIs(sql.references_column(table, 'name'), True) + self.assertIn('COLLATE %s' % editor.quote_name(collation), str(sql)) + # Remove index. + with connection.schema_editor() as editor: + editor.remove_index(Author, index) + self.assertNotIn(index.name, self.get_constraints(table)) + + @skipUnlessDBFeature('supports_expression_indexes') + def test_func_index_calc(self): + with connection.schema_editor() as editor: + editor.create_model(Author) + index = Index(F('height') / (F('weight') + Value(5)), name='func_calc_idx') + # Add index. + with connection.schema_editor() as editor: + editor.add_index(Author, index) + sql = index.create_sql(Author, editor) + table = Author._meta.db_table + self.assertIn(index.name, self.get_constraints(table)) + # SQL contains columns and expressions. + self.assertIs(sql.references_column(table, 'height'), True) + self.assertIs(sql.references_column(table, 'weight'), True) + sql = str(sql) + self.assertIs( + sql.index(editor.quote_name('height')) < + sql.index('/') < + sql.index(editor.quote_name('weight')) < + sql.index('+') < + sql.index('5'), + True, + ) + # Remove index. + with connection.schema_editor() as editor: + editor.remove_index(Author, index) + self.assertNotIn(index.name, self.get_constraints(table)) + + @skipUnlessDBFeature('supports_expression_indexes', 'supports_json_field') + @isolate_apps('schema') + def test_func_index_json_key_transform(self): + class JSONModel(Model): + field = JSONField() + + class Meta: + app_label = 'schema' + + with connection.schema_editor() as editor: + editor.create_model(JSONModel) + self.isolated_local_models = [JSONModel] + index = Index('field__some_key', name='func_json_key_idx') + with connection.schema_editor() as editor: + editor.add_index(JSONModel, index) + sql = index.create_sql(JSONModel, editor) + table = JSONModel._meta.db_table + self.assertIn(index.name, self.get_constraints(table)) + self.assertIs(sql.references_column(table, 'field'), True) + with connection.schema_editor() as editor: + editor.remove_index(JSONModel, index) + self.assertNotIn(index.name, self.get_constraints(table)) + + @skipUnlessDBFeature('supports_expression_indexes', 'supports_json_field') + @isolate_apps('schema') + def test_func_index_json_key_transform_cast(self): + class JSONModel(Model): + field = JSONField() + + class Meta: + app_label = 'schema' + + with connection.schema_editor() as editor: + editor.create_model(JSONModel) + self.isolated_local_models = [JSONModel] + index = Index( + Cast(KeyTextTransform('some_key', 'field'), IntegerField()), + name='func_json_key_cast_idx', + ) + with connection.schema_editor() as editor: + editor.add_index(JSONModel, index) + sql = index.create_sql(JSONModel, editor) + table = JSONModel._meta.db_table + self.assertIn(index.name, self.get_constraints(table)) + self.assertIs(sql.references_column(table, 'field'), True) + with connection.schema_editor() as editor: + editor.remove_index(JSONModel, index) + self.assertNotIn(index.name, self.get_constraints(table)) + + @skipIfDBFeature('supports_expression_indexes') + def test_func_index_unsupported(self): + # Index is ignored on databases that don't support indexes on + # expressions. + with connection.schema_editor() as editor: + editor.create_model(Author) + index = Index(F('name'), name='random_idx') + with connection.schema_editor() as editor, self.assertNumQueries(0): + self.assertIsNone(editor.add_index(Author, index)) + self.assertIsNone(editor.remove_index(Author, index)) + + @skipUnlessDBFeature('supports_expression_indexes') + def test_func_index_nonexistent_field(self): + index = Index(Lower('nonexistent'), name='func_nonexistent_idx') + msg = ( + "Cannot resolve keyword 'nonexistent' into field. Choices are: " + "height, id, name, uuid, weight" + ) + with self.assertRaisesMessage(FieldError, msg): + with connection.schema_editor() as editor: + editor.add_index(Author, index) + + @skipUnlessDBFeature('supports_expression_indexes') + def test_func_index_nondeterministic(self): + with connection.schema_editor() as editor: + editor.create_model(Author) + index = Index(Random(), name='func_random_idx') + with connection.schema_editor() as editor: + with self.assertRaises(DatabaseError): + editor.add_index(Author, index) + def test_primary_key(self): """ Tests altering of the primary key |