Ticket #30246: test_regression.py

import datetime
from decimal import Decimal
from django.db.models import (
    Q, Sum,
)
from django.db.models.functions import Extract
from django.test import TestCase

from .models import (
    Author, Book, Publisher, Store
)

class DateTimesTests(TestCase):
    @classmethod
    def setUpTestData(cls):
        cls.a1 = Author.objects.create(name='Adrian Holovaty', age=34)
        cls.a2 = Author.objects.create(name='Jacob Kaplan-Moss', age=35)
        cls.a3 = Author.objects.create(name='Brad Dayley', age=45)
        cls.a4 = Author.objects.create(name='James Bennett', age=29)
        cls.a5 = Author.objects.create(name='Jeffrey Forcier', age=37)
        cls.a6 = Author.objects.create(name='Paul Bissex', age=29)
        cls.a7 = Author.objects.create(name='Wesley J. Chun', age=25)
        cls.a8 = Author.objects.create(name='Peter Norvig', age=57)
        cls.a9 = Author.objects.create(name='Stuart Russell', age=46)
        cls.a1.friends.add(cls.a2, cls.a4)
        cls.a2.friends.add(cls.a1, cls.a7)
        cls.a4.friends.add(cls.a1)
        cls.a5.friends.add(cls.a6, cls.a7)
        cls.a6.friends.add(cls.a5, cls.a7)
        cls.a7.friends.add(cls.a2, cls.a5, cls.a6)
        cls.a8.friends.add(cls.a9)
        cls.a9.friends.add(cls.a8)

        cls.p1 = Publisher.objects.create(name='Apress', num_awards=3)
        cls.p2 = Publisher.objects.create(name='Sams', num_awards=1)
        cls.p3 = Publisher.objects.create(name='Prentice Hall', num_awards=7)
        cls.p4 = Publisher.objects.create(name='Morgan Kaufmann', num_awards=9)
        cls.p5 = Publisher.objects.create(name="Jonno's House of Books", num_awards=0)

        cls.b1 = Book.objects.create(
            isbn='159059725', name='The Definitive Guide to Django: Web Development Done Right',
            pages=447, rating=4.5, price=Decimal('30.00'), contact=cls.a1, publisher=cls.p1,
            pubdate=datetime.date(2007, 12, 6)
        )
        cls.b2 = Book.objects.create(
            isbn='067232959', name='Sams Teach Yourself Django in 24 Hours',
            pages=528, rating=3.0, price=Decimal('23.09'), contact=cls.a3, publisher=cls.p2,
            pubdate=datetime.date(2008, 3, 3)
        )
        cls.b3 = Book.objects.create(
            isbn='159059996', name='Practical Django Projects',
            pages=300, rating=4.0, price=Decimal('29.69'), contact=cls.a4, publisher=cls.p1,
            pubdate=datetime.date(2008, 6, 23)
        )
        cls.b4 = Book.objects.create(
            isbn='013235613', name='Python Web Development with Django',
            pages=350, rating=4.0, price=Decimal('29.69'), contact=cls.a5, publisher=cls.p3,
            pubdate=datetime.date(2008, 11, 3)
        )

        cls.b1.authors.add(cls.a1, cls.a2)
        cls.b2.authors.add(cls.a3)
        cls.b3.authors.add(cls.a4)
        cls.b4.authors.add(cls.a5, cls.a6, cls.a7)

        s1 = Store.objects.create(
            name='Amazon.com',
            original_opening=datetime.datetime(1994, 4, 23, 9, 17, 42),
            friday_night_closing=datetime.time(23, 59, 59)
        )
        s2 = Store.objects.create(
            name='Books.com',
            original_opening=datetime.datetime(2001, 3, 15, 11, 23, 37),
            friday_night_closing=datetime.time(23, 59, 59)
        )
        s3 = Store.objects.create(
            name="Mamma and Pappa's Books",
            original_opening=datetime.datetime(1945, 4, 25, 16, 24, 14),
            friday_night_closing=datetime.time(21, 30)
        )
        s1.books.add(cls.b1, cls.b2, cls.b3, cls.b4,)
        s2.books.add(cls.b1, cls.b3)
        s3.books.add(cls.b3, cls.b4)

    def test_annotation_with_value(self):
        hour_aggregates = {}
        for i in range(24):
            hour_aggregates['{}_{}'.format("am" if i < 12 else "pm", i)] = Sum("books__pages", filter=Q(hour=i))
        usages = Store.objects.annotate(hour=Extract("friday_night_closing", "hour")).aggregate(**hour_aggregates)
        self.assertEqual(usages['am_0'], 3022)