Fixed #3566 -- Added support for aggregation to the ORM. See the documentation for details on usage.

Many thanks to:
 * Nicolas Lara, who worked on this feature during the 2008 Google Summer of Code.
 * Alex Gaynor for his help debugging and fixing a number of issues.
 * Justin Bronn for his help integrating with contrib.gis.
 * Karen Tracey for her help with cross-platform testing.
 * Ian Kelly for his help testing and fixing Oracle support.
 * Malcolm Tredinnick for his invaluable review notes.

git-svn-id: http://code.djangoproject.com/svn/django/trunk@9742 bcc190cf-cafb-0310-a4f2-bffc1f526a37

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...}
+
+
+"""
+