Debian Codegraph @

tests/indexes/tests.py

raw
1 import datetime
2 from unittest import skipUnless
3
4 from django.conf import settings
5 from django.db import connection
6 from django.db.models import CASCADE, CharField, ForeignKey, Index, Q
7 from django.db.models.functions import Lower
8 from django.test import (
9 TestCase,
10 TransactionTestCase,
11 skipIfDBFeature,
12 skipUnlessDBFeature,
13 )
14 from django.test.utils import override_settings
15 from django.utils import timezone
16
17 from .models import Article, ArticleTranslation, IndexedArticle2
18
19
20 class SchemaIndexesTests(TestCase):
21 """
22 Test index handling by the db.backends.schema infrastructure.
23 """
24
25 def test_index_name_hash(self):
26 """
27 Index names should be deterministic.
28 """
29 editor = connection.schema_editor()
30 index_name = editor._create_index_name(
31 table_name=Article._meta.db_table,
32 column_names=("c1",),
33 suffix="123",
34 )
35 self.assertEqual(index_name, "indexes_article_c1_a52bd80b123")
36
37 def test_index_name(self):
38 """
39 Index names on the built-in database backends::
40 * Are truncated as needed.
41 * Include all the column names.
42 * Include a deterministic hash.
43 """
44 long_name = "l%sng" % ("o" * 100)
45 editor = connection.schema_editor()
46 index_name = editor._create_index_name(
47 table_name=Article._meta.db_table,
48 column_names=("c1", "c2", long_name),
49 suffix="ix",
50 )
51 expected = {
52 "mysql": "indexes_article_c1_c2_looooooooooooooooooo_255179b2ix",
53 "oracle": "indexes_a_c1_c2_loo_255179b2ix",
54 "postgresql": "indexes_article_c1_c2_loooooooooooooooooo_255179b2ix",
55 "sqlite": "indexes_article_c1_c2_l%sng_255179b2ix" % ("o" * 100),
56 }
57 if connection.vendor not in expected:
58 self.skipTest(
59 "This test is only supported on the built-in database backends."
60 )
61 self.assertEqual(index_name, expected[connection.vendor])
62
63 def test_quoted_index_name(self):
64 editor = connection.schema_editor()
65 index_sql = [str(statement) for statement in editor._model_indexes_sql(Article)]
66 self.assertEqual(len(index_sql), 1)
67 # Ensure the index name is properly quoted.
68 self.assertIn(
69 connection.ops.quote_name(Article._meta.indexes[0].name),
70 index_sql[0],
71 )
72
73 def test_columns_list_sql(self):
74 index = Index(fields=["headline"], name="whitespace_idx")
75 editor = connection.schema_editor()
76 self.assertIn(
77 "(%s)" % editor.quote_name("headline"),
78 str(index.create_sql(Article, editor)),
79 )
80
81 @skipUnlessDBFeature("supports_index_column_ordering")
82 def test_descending_columns_list_sql(self):
83 index = Index(fields=["-headline"], name="whitespace_idx")
84 editor = connection.schema_editor()
85 self.assertIn(
86 "(%s DESC)" % editor.quote_name("headline"),
87 str(index.create_sql(Article, editor)),
88 )
89
90 @skipUnlessDBFeature("can_create_inline_fk", "can_rollback_ddl")
91 def test_alter_field_unique_false_removes_deferred_sql(self):
92 field_added = CharField(max_length=127, unique=True)
93 field_added.set_attributes_from_name("charfield_added")
94
95 field_to_alter = CharField(max_length=127, unique=True)
96 field_to_alter.set_attributes_from_name("charfield_altered")
97 altered_field = CharField(max_length=127, unique=False)
98 altered_field.set_attributes_from_name("charfield_altered")
99
100 with connection.schema_editor() as editor:
101 editor.add_field(ArticleTranslation, field_added)
102 editor.add_field(ArticleTranslation, field_to_alter)
103 self.assertEqual(len(editor.deferred_sql), 2)
104 editor.alter_field(ArticleTranslation, field_to_alter, altered_field)
105 self.assertEqual(len(editor.deferred_sql), 1)
106 self.assertIn("charfield_added", str(editor.deferred_sql[0].parts["name"]))
107
108
109 class SchemaIndexesNotPostgreSQLTests(TransactionTestCase):
110 available_apps = ["indexes"]
111
112 def test_create_index_ignores_opclasses(self):
113 index = Index(
114 name="test_ops_class",
115 fields=["headline"],
116 opclasses=["varchar_pattern_ops"],
117 )
118 with connection.schema_editor() as editor:
119 # This would error if opclasses weren't ignored.
120 editor.add_index(IndexedArticle2, index)
121
122
123 # The `condition` parameter is ignored by databases that don't support partial
124 # indexes.
125 @skipIfDBFeature("supports_partial_indexes")
126 class PartialIndexConditionIgnoredTests(TransactionTestCase):
127 available_apps = ["indexes"]
128
129 def test_condition_ignored(self):
130 index = Index(
131 name="test_condition_ignored",
132 fields=["published"],
133 condition=Q(published=True),
134 )
135 with connection.schema_editor() as editor:
136 # This would error if condition weren't ignored.
137 editor.add_index(Article, index)
138
139 self.assertNotIn(
140 "WHERE %s" % editor.quote_name("published"),
141 str(index.create_sql(Article, editor)),
142 )
143
144
145 @skipUnless(connection.vendor == "postgresql", "PostgreSQL tests")
146 class SchemaIndexesPostgreSQLTests(TransactionTestCase):
147 available_apps = ["indexes"]
148 get_opclass_query = """
149 SELECT opcname, c.relname FROM pg_opclass AS oc
150 JOIN pg_index as i on oc.oid = ANY(i.indclass)
151 JOIN pg_class as c on c.oid = i.indexrelid
152 WHERE c.relname = '%s'
153 """
154
155 def test_text_indexes(self):
156 """Test creation of PostgreSQL-specific text indexes (#12234)"""
157 from .models import IndexedArticle
158
159 index_sql = [
160 str(statement)
161 for statement in connection.schema_editor()._model_indexes_sql(
162 IndexedArticle
163 )
164 ]
165 self.assertEqual(len(index_sql), 5)
166 self.assertIn('("headline" varchar_pattern_ops)', index_sql[1])
167 self.assertIn('("body" text_pattern_ops)', index_sql[3])
168 # unique=True and db_index=True should only create the varchar-specific
169 # index (#19441).
170 self.assertIn('("slug" varchar_pattern_ops)', index_sql[4])
171
172 def test_virtual_relation_indexes(self):
173 """Test indexes are not created for related objects"""
174 index_sql = connection.schema_editor()._model_indexes_sql(Article)
175 self.assertEqual(len(index_sql), 1)
176
177 def test_ops_class(self):
178 index = Index(
179 name="test_ops_class",
180 fields=["headline"],
181 opclasses=["varchar_pattern_ops"],
182 )
183 with connection.schema_editor() as editor:
184 editor.add_index(IndexedArticle2, index)
185 with editor.connection.cursor() as cursor:
186 cursor.execute(self.get_opclass_query % "test_ops_class")
187 self.assertEqual(
188 cursor.fetchall(), [("varchar_pattern_ops", "test_ops_class")]
189 )
190
191 def test_ops_class_multiple_columns(self):
192 index = Index(
193 name="test_ops_class_multiple",
194 fields=["headline", "body"],
195 opclasses=["varchar_pattern_ops", "text_pattern_ops"],
196 )
197 with connection.schema_editor() as editor:
198 editor.add_index(IndexedArticle2, index)
199 with editor.connection.cursor() as cursor:
200 cursor.execute(self.get_opclass_query % "test_ops_class_multiple")
201 expected_ops_classes = (
202 ("varchar_pattern_ops", "test_ops_class_multiple"),
203 ("text_pattern_ops", "test_ops_class_multiple"),
204 )
205 self.assertCountEqual(cursor.fetchall(), expected_ops_classes)
206
207 def test_ops_class_partial(self):
208 index = Index(
209 name="test_ops_class_partial",
210 fields=["body"],
211 opclasses=["text_pattern_ops"],
212 condition=Q(headline__contains="China"),
213 )
214 with connection.schema_editor() as editor:
215 editor.add_index(IndexedArticle2, index)
216 with editor.connection.cursor() as cursor:
217 cursor.execute(self.get_opclass_query % "test_ops_class_partial")
218 self.assertCountEqual(
219 cursor.fetchall(), [("text_pattern_ops", "test_ops_class_partial")]
220 )
221
222 def test_ops_class_partial_tablespace(self):
223 indexname = "test_ops_class_tblspace"
224 index = Index(
225 name=indexname,
226 fields=["body"],
227 opclasses=["text_pattern_ops"],
228 condition=Q(headline__contains="China"),
229 db_tablespace="pg_default",
230 )
231 with connection.schema_editor() as editor:
232 editor.add_index(IndexedArticle2, index)
233 self.assertIn(
234 'TABLESPACE "pg_default" ',
235 str(index.create_sql(IndexedArticle2, editor)),
236 )
237 with editor.connection.cursor() as cursor:
238 cursor.execute(self.get_opclass_query % indexname)
239 self.assertCountEqual(cursor.fetchall(), [("text_pattern_ops", indexname)])
240
241 def test_ops_class_descending(self):
242 indexname = "test_ops_class_ordered"
243 index = Index(
244 name=indexname,
245 fields=["-body"],
246 opclasses=["text_pattern_ops"],
247 )
248 with connection.schema_editor() as editor:
249 editor.add_index(IndexedArticle2, index)
250 with editor.connection.cursor() as cursor:
251 cursor.execute(self.get_opclass_query % indexname)
252 self.assertCountEqual(cursor.fetchall(), [("text_pattern_ops", indexname)])
253
254 def test_ops_class_descending_partial(self):
255 indexname = "test_ops_class_ordered_partial"
256 index = Index(
257 name=indexname,
258 fields=["-body"],
259 opclasses=["text_pattern_ops"],
260 condition=Q(headline__contains="China"),
261 )
262 with connection.schema_editor() as editor:
263 editor.add_index(IndexedArticle2, index)
264 with editor.connection.cursor() as cursor:
265 cursor.execute(self.get_opclass_query % indexname)
266 self.assertCountEqual(cursor.fetchall(), [("text_pattern_ops", indexname)])
267
268 @skipUnlessDBFeature("supports_covering_indexes")
269 def test_ops_class_include(self):
270 index_name = "test_ops_class_include"
271 index = Index(
272 name=index_name,
273 fields=["body"],
274 opclasses=["text_pattern_ops"],
275 include=["headline"],
276 )
277 with connection.schema_editor() as editor:
278 editor.add_index(IndexedArticle2, index)
279 with editor.connection.cursor() as cursor:
280 cursor.execute(self.get_opclass_query % index_name)
281 self.assertCountEqual(cursor.fetchall(), [("text_pattern_ops", index_name)])
282
283 @skipUnlessDBFeature("supports_covering_indexes")
284 def test_ops_class_include_tablespace(self):
285 index_name = "test_ops_class_include_tblspace"
286 index = Index(
287 name=index_name,
288 fields=["body"],
289 opclasses=["text_pattern_ops"],
290 include=["headline"],
291 db_tablespace="pg_default",
292 )
293 with connection.schema_editor() as editor:
294 editor.add_index(IndexedArticle2, index)
295 self.assertIn(
296 'TABLESPACE "pg_default"',
297 str(index.create_sql(IndexedArticle2, editor)),
298 )
299 with editor.connection.cursor() as cursor:
300 cursor.execute(self.get_opclass_query % index_name)
301 self.assertCountEqual(cursor.fetchall(), [("text_pattern_ops", index_name)])
302
303 def test_ops_class_columns_lists_sql(self):
304 index = Index(
305 fields=["headline"],
306 name="whitespace_idx",
307 opclasses=["text_pattern_ops"],
308 )
309 with connection.schema_editor() as editor:
310 self.assertIn(
311 "(%s text_pattern_ops)" % editor.quote_name("headline"),
312 str(index.create_sql(Article, editor)),
313 )
314
315 def test_ops_class_descending_columns_list_sql(self):
316 index = Index(
317 fields=["-headline"],
318 name="whitespace_idx",
319 opclasses=["text_pattern_ops"],
320 )
321 with connection.schema_editor() as editor:
322 self.assertIn(
323 "(%s text_pattern_ops DESC)" % editor.quote_name("headline"),
324 str(index.create_sql(Article, editor)),
325 )
326
327
328 @skipUnless(connection.vendor == "mysql", "MySQL tests")
329 class SchemaIndexesMySQLTests(TransactionTestCase):
330 available_apps = ["indexes"]
331
332 def test_no_index_for_foreignkey(self):
333 """
334 MySQL on InnoDB already creates indexes automatically for foreign keys.
335 (#14180). An index should be created if db_constraint=False (#26171).
336 """
337 with connection.cursor() as cursor:
338 storage = connection.introspection.get_storage_engine(
339 cursor,
340 ArticleTranslation._meta.db_table,
341 )
342 if storage != "InnoDB":
343 self.skipTest("This test only applies to the InnoDB storage engine")
344 index_sql = [
345 str(statement)
346 for statement in connection.schema_editor()._model_indexes_sql(
347 ArticleTranslation
348 )
349 ]
350 self.assertEqual(
351 index_sql,
352 [
353 "CREATE INDEX "
354 "`indexes_articletranslation_article_no_constraint_id_d6c0806b` "
355 "ON `indexes_articletranslation` (`article_no_constraint_id`)"
356 ],
357 )
358
359 # The index also shouldn't be created if the ForeignKey is added after
360 # the model was created.
361 field_created = False
362 try:
363 with connection.schema_editor() as editor:
364 new_field = ForeignKey(Article, CASCADE)
365 new_field.set_attributes_from_name("new_foreign_key")
366 editor.add_field(ArticleTranslation, new_field)
367 field_created = True
368 # No deferred SQL. The FK constraint is included in the
369 # statement to add the field.
370 self.assertFalse(editor.deferred_sql)
371 finally:
372 if field_created:
373 with connection.schema_editor() as editor:
374 editor.remove_field(ArticleTranslation, new_field)
375
376
377 @skipUnlessDBFeature("supports_partial_indexes")
378 # SQLite doesn't support timezone-aware datetimes when USE_TZ is False.
379 @override_settings(USE_TZ=True)
380 class PartialIndexTests(TransactionTestCase):
381 # Schema editor is used to create the index to test that it works.
382 available_apps = ["indexes"]
383
384 def test_partial_index(self):
385 with connection.schema_editor() as editor:
386 index = Index(
387 name="recent_article_idx",
388 fields=["pub_date"],
389 condition=Q(
390 pub_date__gt=datetime.datetime(
391 year=2015,
392 month=1,
393 day=1,
394 # PostgreSQL would otherwise complain about the lookup
395 # being converted to a mutable function (by removing
396 # the timezone in the cast) which is forbidden.
397 tzinfo=timezone.get_current_timezone(),
398 ),
399 ),
400 )
401 self.assertIn(
402 "WHERE %s" % editor.quote_name("pub_date"),
403 str(index.create_sql(Article, schema_editor=editor)),
404 )
405 editor.add_index(index=index, model=Article)
406 with connection.cursor() as cursor:
407 self.assertIn(
408 index.name,
409 connection.introspection.get_constraints(
410 cursor=cursor,
411 table_name=Article._meta.db_table,
412 ),
413 )
414 editor.remove_index(index=index, model=Article)
415
416 def test_integer_restriction_partial(self):
417 with connection.schema_editor() as editor:
418 index = Index(
419 name="recent_article_idx",
420 fields=["id"],
421 condition=Q(pk__gt=1),
422 )
423 self.assertIn(
424 "WHERE %s" % editor.quote_name("id"),
425 str(index.create_sql(Article, schema_editor=editor)),
426 )
427 editor.add_index(index=index, model=Article)
428 with connection.cursor() as cursor:
429 self.assertIn(
430 index.name,
431 connection.introspection.get_constraints(
432 cursor=cursor,
433 table_name=Article._meta.db_table,
434 ),
435 )
436 editor.remove_index(index=index, model=Article)
437
438 def test_boolean_restriction_partial(self):
439 with connection.schema_editor() as editor:
440 index = Index(
441 name="published_index",
442 fields=["published"],
443 condition=Q(published=True),
444 )
445 self.assertIn(
446 "WHERE %s" % editor.quote_name("published"),
447 str(index.create_sql(Article, schema_editor=editor)),
448 )
449 editor.add_index(index=index, model=Article)
450 with connection.cursor() as cursor:
451 self.assertIn(
452 index.name,
453 connection.introspection.get_constraints(
454 cursor=cursor,
455 table_name=Article._meta.db_table,
456 ),
457 )
458 editor.remove_index(index=index, model=Article)
459
460 @skipUnlessDBFeature("supports_functions_in_partial_indexes")
461 def test_multiple_conditions(self):
462 with connection.schema_editor() as editor:
463 index = Index(
464 name="recent_article_idx",
465 fields=["pub_date", "headline"],
466 condition=(
467 Q(
468 pub_date__gt=datetime.datetime(
469 year=2015,
470 month=1,
471 day=1,
472 tzinfo=timezone.get_current_timezone(),
473 )
474 )
475 & Q(headline__contains="China")
476 ),
477 )
478 sql = str(index.create_sql(Article, schema_editor=editor))
479 where = sql.find("WHERE")
480 self.assertIn("WHERE (%s" % editor.quote_name("pub_date"), sql)
481 # Because each backend has different syntax for the operators,
482 # check ONLY the occurrence of headline in the SQL.
483 self.assertGreater(sql.rfind("headline"), where)
484 editor.add_index(index=index, model=Article)
485 with connection.cursor() as cursor:
486 self.assertIn(
487 index.name,
488 connection.introspection.get_constraints(
489 cursor=cursor,
490 table_name=Article._meta.db_table,
491 ),
492 )
493 editor.remove_index(index=index, model=Article)
494
495 def test_is_null_condition(self):
496 with connection.schema_editor() as editor:
497 index = Index(
498 name="recent_article_idx",
499 fields=["pub_date"],
500 condition=Q(pub_date__isnull=False),
501 )
502 self.assertIn(
503 "WHERE %s IS NOT NULL" % editor.quote_name("pub_date"),
504 str(index.create_sql(Article, schema_editor=editor)),
505 )
506 editor.add_index(index=index, model=Article)
507 with connection.cursor() as cursor:
508 self.assertIn(
509 index.name,
510 connection.introspection.get_constraints(
511 cursor=cursor,
512 table_name=Article._meta.db_table,
513 ),
514 )
515 editor.remove_index(index=index, model=Article)
516
517 @skipUnlessDBFeature("supports_expression_indexes")
518 def test_partial_func_index(self):
519 index_name = "partial_func_idx"
520 index = Index(
521 Lower("headline").desc(),
522 name=index_name,
523 condition=Q(pub_date__isnull=False),
524 )
525 with connection.schema_editor() as editor:
526 editor.add_index(index=index, model=Article)
527 sql = index.create_sql(Article, schema_editor=editor)
528 table = Article._meta.db_table
529 self.assertIs(sql.references_column(table, "headline"), True)
530 sql = str(sql)
531 self.assertIn("LOWER(%s)" % editor.quote_name("headline"), sql)
532 self.assertIn(
533 "WHERE %s IS NOT NULL" % editor.quote_name("pub_date"),
534 sql,
535 )
536 self.assertGreater(sql.find("WHERE"), sql.find("LOWER"))
537 with connection.cursor() as cursor:
538 constraints = connection.introspection.get_constraints(
539 cursor=cursor,
540 table_name=table,
541 )
542 self.assertIn(index_name, constraints)
543 if connection.features.supports_index_column_ordering:
544 self.assertEqual(constraints[index_name]["orders"], ["DESC"])
545 with connection.schema_editor() as editor:
546 editor.remove_index(Article, index)
547 with connection.cursor() as cursor:
548 self.assertNotIn(
549 index_name,
550 connection.introspection.get_constraints(
551 cursor=cursor,
552 table_name=table,
553 ),
554 )
555
556
557 @skipUnlessDBFeature("supports_covering_indexes")
558 class CoveringIndexTests(TransactionTestCase):
559 available_apps = ["indexes"]
560
561 def test_covering_index(self):
562 index = Index(
563 name="covering_headline_idx",
564 fields=["headline"],
565 include=["pub_date", "published"],
566 )
567 with connection.schema_editor() as editor:
568 self.assertIn(
569 "(%s) INCLUDE (%s, %s)"
570 % (
571 editor.quote_name("headline"),
572 editor.quote_name("pub_date"),
573 editor.quote_name("published"),
574 ),
575 str(index.create_sql(Article, editor)),
576 )
577 editor.add_index(Article, index)
578 with connection.cursor() as cursor:
579 constraints = connection.introspection.get_constraints(
580 cursor=cursor,
581 table_name=Article._meta.db_table,
582 )
583 self.assertIn(index.name, constraints)
584 self.assertEqual(
585 constraints[index.name]["columns"],
586 ["headline", "pub_date", "published"],
587 )
588 editor.remove_index(Article, index)
589 with connection.cursor() as cursor:
590 self.assertNotIn(
591 index.name,
592 connection.introspection.get_constraints(
593 cursor=cursor,
594 table_name=Article._meta.db_table,
595 ),
596 )
597
598 def test_covering_partial_index(self):
599 index = Index(
600 name="covering_partial_headline_idx",
601 fields=["headline"],
602 include=["pub_date"],
603 condition=Q(pub_date__isnull=False),
604 )
605 with connection.schema_editor() as editor:
606 extra_sql = ""
607 if settings.DEFAULT_INDEX_TABLESPACE:
608 extra_sql = "TABLESPACE %s " % editor.quote_name(
609 settings.DEFAULT_INDEX_TABLESPACE
610 )
611 self.assertIn(
612 "(%s) INCLUDE (%s) %sWHERE %s "
613 % (
614 editor.quote_name("headline"),
615 editor.quote_name("pub_date"),
616 extra_sql,
617 editor.quote_name("pub_date"),
618 ),
619 str(index.create_sql(Article, editor)),
620 )
621 editor.add_index(Article, index)
622 with connection.cursor() as cursor:
623 constraints = connection.introspection.get_constraints(
624 cursor=cursor,
625 table_name=Article._meta.db_table,
626 )
627 self.assertIn(index.name, constraints)
628 self.assertEqual(
629 constraints[index.name]["columns"],
630 ["headline", "pub_date"],
631 )
632 editor.remove_index(Article, index)
633 with connection.cursor() as cursor:
634 self.assertNotIn(
635 index.name,
636 connection.introspection.get_constraints(
637 cursor=cursor,
638 table_name=Article._meta.db_table,
639 ),
640 )
641
642 @skipUnlessDBFeature("supports_expression_indexes")
643 def test_covering_func_index(self):
644 index_name = "covering_func_headline_idx"
645 index = Index(Lower("headline"), name=index_name, include=["pub_date"])
646 with connection.schema_editor() as editor:
647 editor.add_index(index=index, model=Article)
648 sql = index.create_sql(Article, schema_editor=editor)
649 table = Article._meta.db_table
650 self.assertIs(sql.references_column(table, "headline"), True)
651 sql = str(sql)
652 self.assertIn("LOWER(%s)" % editor.quote_name("headline"), sql)
653 self.assertIn("INCLUDE (%s)" % editor.quote_name("pub_date"), sql)
654 self.assertGreater(sql.find("INCLUDE"), sql.find("LOWER"))
655 with connection.cursor() as cursor:
656 constraints = connection.introspection.get_constraints(
657 cursor=cursor,
658 table_name=table,
659 )
660 self.assertIn(index_name, constraints)
661 self.assertIn("pub_date", constraints[index_name]["columns"])
662 with connection.schema_editor() as editor:
663 editor.remove_index(Article, index)
664 with connection.cursor() as cursor:
665 self.assertNotIn(
666 index_name,
667 connection.introspection.get_constraints(
668 cursor=cursor,
669 table_name=table,
670 ),
671 )
672
673
674 @skipIfDBFeature("supports_covering_indexes")
675 class CoveringIndexIgnoredTests(TransactionTestCase):
676 available_apps = ["indexes"]
677
678 def test_covering_ignored(self):
679 index = Index(
680 name="test_covering_ignored",
681 fields=["headline"],
682 include=["pub_date"],
683 )
684 with connection.schema_editor() as editor:
685 editor.add_index(Article, index)
686 self.assertNotIn(
687 "INCLUDE (%s)" % editor.quote_name("headline"),
688 str(index.create_sql(Article, editor)),
689 )