diff options
Diffstat (limited to 'tests/regressiontests/aggregation_regress')
3 files changed, 428 insertions, 0 deletions
diff --git a/tests/regressiontests/aggregation_regress/__init__.py b/tests/regressiontests/aggregation_regress/__init__.py new file mode 100644 index 0000000000..e69de29bb2 --- /dev/null +++ b/tests/regressiontests/aggregation_regress/__init__.py diff --git a/tests/regressiontests/aggregation_regress/fixtures/initial_data.json b/tests/regressiontests/aggregation_regress/fixtures/initial_data.json new file mode 100644 index 0000000000..a632c4077a --- /dev/null +++ b/tests/regressiontests/aggregation_regress/fixtures/initial_data.json @@ -0,0 +1,229 @@ +[ + { + "pk": 1, + "model": "aggregation_regress.publisher", + "fields": { + "name": "Apress", + "num_awards": 3 + } + }, + { + "pk": 2, + "model": "aggregation_regress.publisher", + "fields": { + "name": "Sams", + "num_awards": 1 + } + }, + { + "pk": 3, + "model": "aggregation_regress.publisher", + "fields": { + "name": "Prentice Hall", + "num_awards": 7 + } + }, + { + "pk": 4, + "model": "aggregation_regress.publisher", + "fields": { + "name": "Morgan Kaufmann", + "num_awards": 9 + } + }, + { + "pk": 1, + "model": "aggregation_regress.book", + "fields": { + "publisher": 1, + "isbn": "159059725", + "name": "The Definitive Guide to Django: Web Development Done Right", + "price": "30.00", + "rating": 4.5, + "authors": [1, 2], + "pages": 447, + "pubdate": "2007-12-6" + } + }, + { + "pk": 2, + "model": "aggregation_regress.book", + "fields": { + "publisher": 2, + "isbn": "067232959", + "name": "Sams Teach Yourself Django in 24 Hours", + "price": "23.09", + "rating": 3.0, + "authors": [3], + "pages": 528, + "pubdate": "2008-3-3" + } + }, + { + "pk": 3, + "model": "aggregation_regress.book", + "fields": { + "publisher": 1, + "isbn": "159059996", + "name": "Practical Django Projects", + "price": "29.69", + "rating": 4.0, + "authors": [4], + "pages": 300, + "pubdate": "2008-6-23" + } + }, + { + "pk": 4, + "model": "aggregation_regress.book", + "fields": { + "publisher": 3, + "isbn": "013235613", + "name": "Python Web Development with Django", + "price": "29.69", + "rating": 4.0, + "authors": [5, 6, 7], + "pages": 350, + "pubdate": "2008-11-3" + } + }, + { + "pk": 5, + "model": "aggregation_regress.book", + "fields": { + "publisher": 3, + "isbn": "013790395", + "name": "Artificial Intelligence: A Modern Approach", + "price": "82.80", + "rating": 4.0, + "authors": [8, 9], + "pages": 1132, + "pubdate": "1995-1-15" + } + }, + { + "pk": 6, + "model": "aggregation_regress.book", + "fields": { + "publisher": 4, + "isbn": "155860191", + "name": "Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp", + "price": "75.00", + "rating": 5.0, + "authors": [8], + "pages": 946, + "pubdate": "1991-10-15" + } + }, + { + "pk": 1, + "model": "aggregation_regress.store", + "fields": { + "books": [1, 2, 3, 4, 5, 6], + "name": "Amazon.com", + "original_opening": "1994-4-23 9:17:42", + "friday_night_closing": "23:59:59" + } + }, + { + "pk": 2, + "model": "aggregation_regress.store", + "fields": { + "books": [1, 3, 5, 6], + "name": "Books.com", + "original_opening": "2001-3-15 11:23:37", + "friday_night_closing": "23:59:59" + } + }, + { + "pk": 3, + "model": "aggregation_regress.store", + "fields": { + "books": [3, 4, 6], + "name": "Mamma and Pappa's Books", + "original_opening": "1945-4-25 16:24:14", + "friday_night_closing": "21:30:00" + } + }, + { + "pk": 1, + "model": "aggregation_regress.author", + "fields": { + "age": 34, + "friends": [2, 4], + "name": "Adrian Holovaty" + } + }, + { + "pk": 2, + "model": "aggregation_regress.author", + "fields": { + "age": 35, + "friends": [1, 7], + "name": "Jacob Kaplan-Moss" + } + }, + { + "pk": 3, + "model": "aggregation_regress.author", + "fields": { + "age": 45, + "friends": [], + "name": "Brad Dayley" + } + }, + { + "pk": 4, + "model": "aggregation_regress.author", + "fields": { + "age": 29, + "friends": [1], + "name": "James Bennett" + } + }, + { + "pk": 5, + "model": "aggregation_regress.author", + "fields": { + "age": 37, + "friends": [6, 7], + "name": "Jeffrey Forcier " + } + }, + { + "pk": 6, + "model": "aggregation_regress.author", + "fields": { + "age": 29, + "friends": [5, 7], + "name": "Paul Bissex" + } + }, + { + "pk": 7, + "model": "aggregation_regress.author", + "fields": { + "age": 25, + "friends": [2, 5, 6], + "name": "Wesley J. Chun" + } + }, + { + "pk": 8, + "model": "aggregation_regress.author", + "fields": { + "age": 57, + "friends": [9], + "name": "Peter Norvig" + } + }, + { + "pk": 9, + "model": "aggregation_regress.author", + "fields": { + "age": 46, + "friends": [8], + "name": "Stuart Russell" + } + } +] diff --git a/tests/regressiontests/aggregation_regress/models.py b/tests/regressiontests/aggregation_regress/models.py new file mode 100644 index 0000000000..cb442148bd --- /dev/null +++ b/tests/regressiontests/aggregation_regress/models.py @@ -0,0 +1,199 @@ +# coding: utf-8 +from django.db import models +from django.conf import settings + +try: + sorted +except NameError: + from django.utils.itercompat import sorted # For Python 2.3 + +class Author(models.Model): + name = models.CharField(max_length=100) + age = models.IntegerField() + friends = models.ManyToManyField('self', blank=True) + + def __unicode__(self): + return self.name + +class Publisher(models.Model): + name = models.CharField(max_length=300) + num_awards = models.IntegerField() + + def __unicode__(self): + return self.name + +class Book(models.Model): + isbn = models.CharField(max_length=9) + name = models.CharField(max_length=300) + pages = models.IntegerField() + rating = models.FloatField() + price = models.DecimalField(decimal_places=2, max_digits=6) + authors = models.ManyToManyField(Author) + publisher = models.ForeignKey(Publisher) + pubdate = models.DateField() + + class Meta: + ordering = ('name',) + + def __unicode__(self): + return self.name + +class Store(models.Model): + name = models.CharField(max_length=300) + books = models.ManyToManyField(Book) + original_opening = models.DateTimeField() + friday_night_closing = models.TimeField() + + def __unicode__(self): + return self.name + +#Extra does not play well with values. Modify the tests if/when this is fixed. +__test__ = {'API_TESTS': """ +>>> from django.core import management +>>> from django.db.models import get_app + +# Reset the database representation of this app. +# This will return the database to a clean initial state. +>>> management.call_command('flush', verbosity=0, interactive=False) + +>>> from django.db.models import Avg, Sum, Count, Max, Min, StdDev, Variance + +# Ordering requests are ignored +>>> Author.objects.all().order_by('name').aggregate(Avg('age')) +{'age__avg': 37.4...} + +# Implicit ordering is also ignored +>>> Book.objects.all().aggregate(Sum('pages')) +{'pages__sum': 3703} + +# Baseline results +>>> Book.objects.all().aggregate(Sum('pages'), Avg('pages')) +{'pages__sum': 3703, 'pages__avg': 617.1...} + +# Empty values query doesn't affect grouping or results +>>> Book.objects.all().values().aggregate(Sum('pages'), Avg('pages')) +{'pages__sum': 3703, 'pages__avg': 617.1...} + +# Aggregate overrides extra selected column +>>> Book.objects.all().extra(select={'price_per_page' : 'price / pages'}).aggregate(Sum('pages')) +{'pages__sum': 3703} + +# Annotations get combined with extra select clauses +>>> sorted(Book.objects.all().annotate(mean_auth_age=Avg('authors__age')).extra(select={'manufacture_cost' : 'price * .5'}).get(pk=2).__dict__.items()) +[('id', 2), ('isbn', u'067232959'), ('manufacture_cost', ...11.545...), ('mean_auth_age', 45.0), ('name', u'Sams Teach Yourself Django in 24 Hours'), ('pages', 528), ('price', Decimal("23.09")), ('pubdate', datetime.date(2008, 3, 3)), ('publisher_id', 2), ('rating', 3.0)] + +# Order of the annotate/extra in the query doesn't matter +>>> sorted(Book.objects.all().extra(select={'manufacture_cost' : 'price * .5'}).annotate(mean_auth_age=Avg('authors__age')).get(pk=2).__dict__.items()) +[('id', 2), ('isbn', u'067232959'), ('manufacture_cost', ...11.545...), ('mean_auth_age', 45.0), ('name', u'Sams Teach Yourself Django in 24 Hours'), ('pages', 528), ('price', Decimal("23.09")), ('pubdate', datetime.date(2008, 3, 3)), ('publisher_id', 2), ('rating', 3.0)] + +# Values queries can be combined with annotate and extra +>>> sorted(Book.objects.all().annotate(mean_auth_age=Avg('authors__age')).extra(select={'manufacture_cost' : 'price * .5'}).values().get(pk=2).items()) +[('id', 2), ('isbn', u'067232959'), ('manufacture_cost', ...11.545...), ('mean_auth_age', 45.0), ('name', u'Sams Teach Yourself Django in 24 Hours'), ('pages', 528), ('price', Decimal("23.09")), ('pubdate', datetime.date(2008, 3, 3)), ('publisher_id', 2), ('rating', 3.0)] + +# The order of the values, annotate and extra clauses doesn't matter +>>> sorted(Book.objects.all().values().annotate(mean_auth_age=Avg('authors__age')).extra(select={'manufacture_cost' : 'price * .5'}).get(pk=2).items()) +[('id', 2), ('isbn', u'067232959'), ('manufacture_cost', ...11.545...), ('mean_auth_age', 45.0), ('name', u'Sams Teach Yourself Django in 24 Hours'), ('pages', 528), ('price', Decimal("23.09")), ('pubdate', datetime.date(2008, 3, 3)), ('publisher_id', 2), ('rating', 3.0)] + +# A values query that selects specific columns reduces the output +>>> sorted(Book.objects.all().annotate(mean_auth_age=Avg('authors__age')).extra(select={'price_per_page' : 'price / pages'}).values('name').get(pk=1).items()) +[('mean_auth_age', 34.5), ('name', u'The Definitive Guide to Django: Web Development Done Right')] + +# The annotations are added to values output if values() precedes annotate() +>>> sorted(Book.objects.all().values('name').annotate(mean_auth_age=Avg('authors__age')).extra(select={'price_per_page' : 'price / pages'}).get(pk=1).items()) +[('mean_auth_age', 34.5), ('name', u'The Definitive Guide to Django: Web Development Done Right')] + +# Check that all of the objects are getting counted (allow_nulls) and that values respects the amount of objects +>>> len(Author.objects.all().annotate(Avg('friends__age')).values()) +9 + +# Check that consecutive calls to annotate accumulate in the query +>>> Book.objects.values('price').annotate(oldest=Max('authors__age')).order_by('oldest', 'price').annotate(Max('publisher__num_awards')) +[{'price': Decimal("30..."), 'oldest': 35, 'publisher__num_awards__max': 3}, {'price': Decimal("29.69"), 'oldest': 37, 'publisher__num_awards__max': 7}, {'price': Decimal("23.09"), 'oldest': 45, 'publisher__num_awards__max': 1}, {'price': Decimal("75..."), 'oldest': 57, 'publisher__num_awards__max': 9}, {'price': Decimal("82.8..."), 'oldest': 57, 'publisher__num_awards__max': 7}] + +# Aggregates can be composed over annotations. +# The return type is derived from the composed aggregate +>>> Book.objects.all().annotate(num_authors=Count('authors__id')).aggregate(Max('pages'), Max('price'), Sum('num_authors'), Avg('num_authors')) +{'num_authors__sum': 10, 'num_authors__avg': 1.66..., 'pages__max': 1132, 'price__max': Decimal("82.80")} + +# Bad field requests in aggregates are caught and reported +>>> Book.objects.all().aggregate(num_authors=Count('foo')) +Traceback (most recent call last): +... +FieldError: Cannot resolve keyword 'foo' into field. Choices are: authors, id, isbn, name, pages, price, pubdate, publisher, rating, store + +>>> Book.objects.all().annotate(num_authors=Count('foo')) +Traceback (most recent call last): +... +FieldError: Cannot resolve keyword 'foo' into field. Choices are: authors, id, isbn, name, pages, price, pubdate, publisher, rating, store + +>>> Book.objects.all().annotate(num_authors=Count('authors__id')).aggregate(Max('foo')) +Traceback (most recent call last): +... +FieldError: Cannot resolve keyword 'foo' into field. Choices are: authors, id, isbn, name, pages, price, pubdate, publisher, rating, store, num_authors + +# Old-style count aggregations can be mixed with new-style +>>> Book.objects.annotate(num_authors=Count('authors')).count() +6 + +# Non-ordinal, non-computed Aggregates over annotations correctly inherit +# the annotation's internal type if the annotation is ordinal or computed +>>> Book.objects.annotate(num_authors=Count('authors')).aggregate(Max('num_authors')) +{'num_authors__max': 3} + +>>> Publisher.objects.annotate(avg_price=Avg('book__price')).aggregate(Max('avg_price')) +{'avg_price__max': 75.0...} + +# Aliases are quoted to protected aliases that might be reserved names +>>> Book.objects.aggregate(number=Max('pages'), select=Max('pages')) +{'number': 1132, 'select': 1132} + + +""" +} + +if settings.DATABASE_ENGINE != 'sqlite3': + __test__['API_TESTS'] += """ +# Stddev and Variance are not guaranteed to be available for SQLite. + +>>> Book.objects.aggregate(StdDev('pages')) +{'pages__stddev': 311.46...} + +>>> Book.objects.aggregate(StdDev('rating')) +{'rating__stddev': 0.60...} + +>>> Book.objects.aggregate(StdDev('price')) +{'price__stddev': 24.16...} + + +>>> Book.objects.aggregate(StdDev('pages', sample=True)) +{'pages__stddev': 341.19...} + +>>> Book.objects.aggregate(StdDev('rating', sample=True)) +{'rating__stddev': 0.66...} + +>>> Book.objects.aggregate(StdDev('price', sample=True)) +{'price__stddev': 26.46...} + + +>>> Book.objects.aggregate(Variance('pages')) +{'pages__variance': 97010.80...} + +>>> Book.objects.aggregate(Variance('rating')) +{'rating__variance': 0.36...} + +>>> Book.objects.aggregate(Variance('price')) +{'price__variance': 583.77...} + + +>>> Book.objects.aggregate(Variance('pages', sample=True)) +{'pages__variance': 116412.96...} + +>>> Book.objects.aggregate(Variance('rating', sample=True)) +{'rating__variance': 0.44...} + +>>> Book.objects.aggregate(Variance('price', sample=True)) +{'price__variance': 700.53...} + + +""" + |