def test_annotate_values_list(self):
books = Book.objects.filter(pk=assemble_pk(159059725, "The Definitive Guide to Django: Web Development Done Right")).annotate(mean_age=Avg("authors__age")).values_list("isbn", "mean_age")
self.assertEqual(
list(books), [
("159059725", 34.5),
]
)
books = Book.objects.filter(pk=assemble_pk(159059725, "The Definitive Guide to Django: Web Development Done Right")).annotate(mean_age=Avg("authors__age")).values_list("isbn")
self.assertEqual(
list(books), [
('159059725',)
]
)
books = Book.objects.filter(pk=assemble_pk(159059725, "The Definitive Guide to Django: Web Development Done Right")).annotate(mean_age=Avg("authors__age")).values_list("mean_age")
self.assertEqual(
list(books), [
(34.5,)
]
)
books = Book.objects.filter(pk=assemble_pk(159059725, "The Definitive Guide to Django: Web Development Done Right")).annotate(mean_age=Avg("authors__age")).values_list("mean_age", flat=True)
self.assertEqual(list(books), [34.5])
books = Book.objects.values_list("price").annotate(count=Count("price")).order_by("-count", "price")
self.assertEqual(
list(books), [
(Decimal("29.69"), 2),
(Decimal('23.09'), 1),
(Decimal('30'), 1),
(Decimal('75'), 1),
(Decimal('82.8'), 1),
]
)
def test_select_book_chapter_number(self):
#opts.get_all_field_names
com_pk = assemble_pk("Libro sulle compositeKey", "Simone")
book = Book.objects.create(pk=com_pk)
for n in range(10):
book.chapter_set.create(num=n)
list(Book.objects.filter(chapter_set__num=3))
def test_select_book_chapter_number(self):
#opts.get_all_field_names
com_pk = assemble_pk("Libro sulle compositeKey", "Simone")
book = Book.objects.create(pk=com_pk)
for n in range(10):
book.chapter_set.create(num=n)
list(Book.objects.filter(chapter_set__num=3))
def test_id_before_audit(self):
self.check(CKA, "SELECT *, 'fake' as id, 'Z' as zzz FROM cka", (
("id", assemble_pk("a", "b")),
("k1", "a"),
("k2", "b"),
("v", "c"),
("zzz", "Z"),
))
def test_id_simple(self):
self.check(CK, "SELECT *, 'Z' as zzz FROM ck", (
("id", assemble_pk("a", "b")),
("k1", "a"),
("k2", "b"),
("v", "c"),
("zzz", "Z"),
))
def test_id_before_audit(self):
self.check(CKA, "SELECT *, 'fake' as id, 'Z' as zzz FROM cka", (
("id", assemble_pk("a", "b")),
("k1", "a"),
("k2", "b"),
("v", "c"),
("zzz", "Z"),
))
def test_id_simple(self):
self.check(CK, "SELECT *, 'Z' as zzz FROM ck", (
("id", assemble_pk("a", "b")),
("k1", "a"),
("k2", "b"),
("v", "c"),
("zzz", "Z"),
))
def test_reversibility(self):
# '', None, NOT ammissible
params = [
'ab', 'a' + SEP + 'b', 'a' + ESCAPE_CHAR + SEP + 'b', '123',
'a' + SEP, 'b' + ESCAPE_CHAR, 'c' + ESCAPE_CHAR + SEP, SEP,
ESCAPE_CHAR, ESCAPE_CHAR + SEP, SEP + ESCAPE_CHAR, NONE_CHAR,
'd' + ESCAPE_CHAR + SEP
]
self.assertEquals(params, disassemble_pk(assemble_pk(*params)))
def test_id_after_proxy(self):
self.check(CKP2,
"SELECT *, 'fake' as id, 'Z' as zzz, 'Y' as yyy FROM cka2", (
("id", assemble_pk("a", "b")),
("k1", "a"),
("k2", "b"),
("v", "c"),
("zzz", "Z"),
("yyy", "Y"),
))
def test_id_after_simple(self):
self.check(CK2,
"SELECT *, 'fake' as id, 'Z' as zzz, 'Y' as yyy FROM ck2", (
("id", assemble_pk("a", "b")),
("k1", "a"),
("k2", "b"),
("v", "c"),
("zzz", "Z"),
("yyy", "Y"),
))
def test_id_before_proxy(self):
self.check(CKP,
"SELECT *, 'fake' as id, 'Z' as zzz, 'Y' as yyy FROM cka", (
("id", assemble_pk("a", "b")),
("k1", "a"),
("k2", "b"),
("v", "c"),
("zzz", "Z"),
("yyy", "Y"),
))
def test_manually_specify_primary_key(self):
# You can manually specify the primary key when creating a new object.
a101 = Article(
#id=101,
headline='Article 101',
pub_date=datetime(2005, 7, 31, 12, 30, 45),
)
a101.save()
a101 = Article.objects.get(pk=assemble_pk('Article 101', datetime(2005, 7, 31, 12, 30, 45)))
self.assertEqual(a101.headline, u'Article 101')
def test_manually_specify_primary_key(self):
# You can manually specify the primary key when creating a new object.
a101 = Article(
#id=101,
headline='Article 101',
pub_date=datetime(2005, 7, 31, 12, 30, 45),
)
a101.save()
a101 = Article.objects.get(
pk=assemble_pk('Article 101', datetime(2005, 7, 31, 12, 30, 45)))
self.assertEqual(a101.headline, u'Article 101')
def pre_sql_setup(self):
"""
If the update depends on results from other tables, we need to do some
munging of the "where" conditions to match the format required for
(portable) SQL updates. That is done here.
Further, if we are going to be running multiple updates, we pull out
the id values to update at this point so that they don't change as a
result of the progressive updates.
"""
self.query.select_related = False
self.query.clear_ordering(True)
super(SQLUpdateCompiler, self).pre_sql_setup()
count = self.query.count_active_tables()
if not self.query.related_updates and count == 1:
return
# We need to use a sub-select in the where clause to filter on things
# from other tables.
query = self.query.clone(klass=Query)
query.bump_prefix()
query.extra = {}
query.select = []
query.add_fields([query.model._meta.pk.name])
# Recheck the count - it is possible that fiddling with the select
# fields above removes tables from the query. Refs #18304.
count = query.count_active_tables()
if not self.query.related_updates and count == 1:
return
must_pre_select = count > 1 and not self.connection.features.update_can_self_select
# Now we adjust the current query: reset the where clause and get rid
# of all the tables we don't need (since they're in the sub-select).
self.query.where = self.query.where_class()
if self.query.related_updates or must_pre_select:
# Either we're using the idents in multiple update queries (so
# don't want them to change), or the db backend doesn't support
# selecting from the updating table (e.g. MySQL).
idents = []
multiple = hasattr(query.model._meta.pk, "fields")
if multiple:
query.select = []
query.add_fields([f.name for f in query.model._meta.pk.fields])
for rows in query.get_compiler(self.using).execute_sql(MULTI):
idents.extend(
[assemble_pk(*r) if multiple else r[0] for r in rows])
self.query.add_filter(('pk__in', idents))
self.query.related_ids = idents
else:
# The fast path. Filters and updates in one query.
self.query.add_filter(('pk__in', query))
for alias in self.query.tables[1:]:
self.query.alias_refcount[alias] = 0
def pre_sql_setup(self):
"""
If the update depends on results from other tables, we need to do some
munging of the "where" conditions to match the format required for
(portable) SQL updates. That is done here.
Further, if we are going to be running multiple updates, we pull out
the id values to update at this point so that they don't change as a
result of the progressive updates.
"""
self.query.select_related = False
self.query.clear_ordering(True)
super(SQLUpdateCompiler, self).pre_sql_setup()
count = self.query.count_active_tables()
if not self.query.related_updates and count == 1:
return
# We need to use a sub-select in the where clause to filter on things
# from other tables.
query = self.query.clone(klass=Query)
query.bump_prefix()
query.extra = {}
query.select = []
query.add_fields([query.get_meta().pk.name])
# Recheck the count - it is possible that fiddling with the select
# fields above removes tables from the query. Refs #18304.
count = query.count_active_tables()
if not self.query.related_updates and count == 1:
return
must_pre_select = count > 1 and not self.connection.features.update_can_self_select
# Now we adjust the current query: reset the where clause and get rid
# of all the tables we don't need (since they're in the sub-select).
self.query.where = self.query.where_class()
if self.query.related_updates or must_pre_select:
# Either we're using the idents in multiple update queries (so
# don't want them to change), or the db backend doesn't support
# selecting from the updating table (e.g. MySQL).
idents = []
multiple = hasattr(query.model._meta.pk, "fields")
if multiple:
query.select = []
query.add_fields([f.name for f in query.get_meta().pk.fields])
for rows in query.get_compiler(self.using).execute_sql(MULTI):
idents.extend([assemble_pk(*r) if multiple else r[0] for r in rows])
self.query.add_filter(("pk__in", idents))
self.query.related_ids = idents
else:
# The fast path. Filters and updates in one query.
self.query.add_filter(("pk__in", query))
for alias in self.query.tables[1:]:
self.query.alias_refcount[alias] = 0
def test_annotate_values_list(self):
books = Book.objects.filter(pk=assemble_pk(
159059725,
"The Definitive Guide to Django: Web Development Done Right"
)).annotate(mean_age=Avg("authors__age")).values_list(
"isbn", "mean_age")
self.assertEqual(list(books), [
("159059725", 34.5),
])
books = Book.objects.filter(pk=assemble_pk(
159059725,
"The Definitive Guide to Django: Web Development Done Right"
)).annotate(mean_age=Avg("authors__age")).values_list("isbn")
self.assertEqual(list(books), [('159059725', )])
books = Book.objects.filter(pk=assemble_pk(
159059725,
"The Definitive Guide to Django: Web Development Done Right"
)).annotate(mean_age=Avg("authors__age")).values_list("mean_age")
self.assertEqual(list(books), [(34.5, )])
books = Book.objects.filter(pk=assemble_pk(
159059725,
"The Definitive Guide to Django: Web Development Done Right"
)).annotate(mean_age=Avg("authors__age")).values_list("mean_age",
flat=True)
self.assertEqual(list(books), [34.5])
books = Book.objects.values_list("price").annotate(
count=Count("price")).order_by("-count", "price")
self.assertEqual(list(books), [
(Decimal("29.69"), 2),
(Decimal('23.09'), 1),
(Decimal('30'), 1),
(Decimal('75'), 1),
(Decimal('82.8'), 1),
])
def test_annotation(self):
vals = Author.objects.filter(pk=assemble_pk('Adrian Holovaty', 34)).aggregate(Count("friends__id"))
self.assertEqual(vals, {"friends__id__count": 2})
books = Book.objects.annotate(num_authors=Count("authors__name")).filter(num_authors__ge=2).order_by("pk")
self.assertQuerysetEqual(
books, [
"Artificial Intelligence: A Modern Approach",
"The Definitive Guide to Django: Web Development Done Right",
],
lambda b: b.name
)
authors = Author.objects.annotate(num_friends=Count("friends__id", distinct=True)).filter(num_friends=0).order_by("pk")
self.assertQuerysetEqual(
authors, [
"Brad Dayley",
],
lambda a: a.name
)
publishers = Publisher.objects.annotate(num_books=Count("book__id")).filter(num_books__gt=1).order_by("pk")
self.assertQuerysetEqual(
publishers, [
"Apress",
"Prentice Hall",
],
lambda p: p.name
)
publishers = Publisher.objects.filter(book__price__lt=Decimal("40.0")).annotate(num_books=Count("book__id")).filter(num_books__gt=1)
self.assertQuerysetEqual(
publishers, [
"Apress",
],
lambda p: p.name
)
books = Book.objects.annotate(num_authors=Count("authors__id")).filter(authors__name__contains="Norvig", num_authors__gt=1)
self.assertQuerysetEqual(
books, [
"Artificial Intelligence: A Modern Approach",
],
lambda b: b.name
)
def test_annotation(self):
vals = Author.objects.filter(
pk=assemble_pk('Adrian Holovaty', 34)).aggregate(
Count("friends__id"))
self.assertEqual(vals, {"friends__id__count": 2})
books = Book.objects.annotate(
num_authors=Count("authors__name")).filter(
num_authors__ge=2).order_by("pk")
self.assertQuerysetEqual(books, [
"Artificial Intelligence: A Modern Approach",
"The Definitive Guide to Django: Web Development Done Right",
], lambda b: b.name)
authors = Author.objects.annotate(
num_friends=Count("friends__id", distinct=True)).filter(
num_friends=0).order_by("pk")
self.assertQuerysetEqual(authors, [
"Brad Dayley",
], lambda a: a.name)
publishers = Publisher.objects.annotate(
num_books=Count("book__id")).filter(num_books__gt=1).order_by("pk")
self.assertQuerysetEqual(publishers, [
"Apress",
"Prentice Hall",
], lambda p: p.name)
publishers = Publisher.objects.filter(
book__price__lt=Decimal("40.0")).annotate(
num_books=Count("book__id")).filter(num_books__gt=1)
self.assertQuerysetEqual(publishers, [
"Apress",
], lambda p: p.name)
books = Book.objects.annotate(num_authors=Count("authors__id")).filter(
authors__name__contains="Norvig", num_authors__gt=1)
self.assertQuerysetEqual(books, [
"Artificial Intelligence: A Modern Approach",
], lambda b: b.name)
def test_custom_pk(self):
dan = Employee.objects.create(
employee_code=123, first_name="Dan", last_name="Jones"
)
self.assertQuerysetEqual(
Employee.objects.all(), [
"Dan Jones",
],
unicode
)
fran = Employee.objects.create(
employee_code=456, first_name="Fran", last_name="Bones"
)
self.assertQuerysetEqual(
Employee.objects.all(), [
"Fran Bones",
"Dan Jones",
],
unicode
)
self.assertEqual(Employee.objects.get(pk=assemble_pk("123", "Jones")), dan)
self.assertEqual(Employee.objects.get(pk=assemble_pk(456, "Bones")), fran)
self.assertRaises(Employee.DoesNotExist,
lambda: Employee.objects.get(pk=42)
)
# Use the name of the primary key, rather than pk.
self.assertEqual(Employee.objects.get(employee_code=123), dan)
self.assertEqual(Employee.objects.get(pk=assemble_pk(456, "Bones")), fran)
self.assertEqual(Employee.objects.get(id=assemble_pk("456", "Bones")), fran)
# pk can be used as a substitute for the primary key.
self.assertQuerysetEqual(
Employee.objects.filter(pk__in=[assemble_pk("123", "Jones"), assemble_pk("456","Bones")]), [
"Fran Bones",
"Dan Jones",
],
unicode
)
# The primary key can be accessed via the pk property on the model.
e = Employee.objects.get(pk=assemble_pk("123", "Jones"))
self.assertEqual(e.pk, assemble_pk("123", "Jones"))
# Or we can use the real attribute name for the primary key:
self.assertEqual(e.employee_code, 123)
# Fran got married and changed her last name.
fran = Employee.objects.get(pk=assemble_pk("456", "Bones"))
fran.last_name = "Jones"
fran.save()
self.assertQuerysetEqual(
Employee.objects.filter(last_name="Jones"), [
"Dan Jones",
"Fran Jones",
],
unicode
)
emps = Employee.objects.in_bulk([assemble_pk("123", "Jones"), assemble_pk("456", "Jones")])
self.assertEqual(emps[assemble_pk("123", "Jones")], dan)
b = Business.objects.create(name="Sears")
b.employees.add(dan, fran)
self.assertQuerysetEqual(
b.employees.all(), [
"Dan Jones",
"Fran Jones",
],
unicode
)
self.assertQuerysetEqual(
fran.business_set.all(), [
"Sears",
],
lambda b: b.name
)
self.assertEqual(Business.objects.in_bulk(["Sears"]), {
"Sears": b,
})
self.assertQuerysetEqual(
Business.objects.filter(name="Sears"), [
"Sears"
],
lambda b: b.name
)
self.assertQuerysetEqual(
Business.objects.filter(pk="Sears"), [
"Sears",
],
lambda b: b.name
)
# Queries across tables, involving primary key
self.assertQuerysetEqual(
Employee.objects.filter(business__name="Sears"), [
"Dan Jones",
"Fran Jones",
],
unicode,
)
self.assertQuerysetEqual(
Employee.objects.filter(business__pk="Sears"), [
"Dan Jones",
"Fran Jones",
],
unicode,
)
self.assertQuerysetEqual(
Business.objects.filter(employees__employee_code=123), [
"Sears",
],
lambda b: b.name
)
self.assertQuerysetEqual(
Business.objects.filter(employees__pk=assemble_pk("123", "Jones")), [
"Sears",
],
lambda b: b.name,
)
self.assertQuerysetEqual(
Business.objects.filter(employees__first_name__startswith="Fran"), [
"Sears",
],
lambda b: b.name
)
def test_annotate_values(self):
books = list(
Book.objects.filter(pk=assemble_pk(
159059725,
"The Definitive Guide to Django: Web Development Done Right")).
annotate(mean_age=Avg("authors__age")).values())
self.assertEqual(books, [{
"contact_age": 34,
"contact_name": u'Adrian Holovaty',
"isbn": "159059725",
"mean_age": 34.5,
"name":
"The Definitive Guide to Django: Web Development Done Right",
"pages": 447,
"price": Approximate(Decimal("30")),
"pubdate": datetime.date(2007, 12, 6),
"publisher_id": 1,
"rating": 4.5,
}])
books = Book.objects.filter(pk=assemble_pk(
159059725,
"The Definitive Guide to Django: Web Development Done Right"
)).annotate(mean_age=Avg('authors__age')).values(
'pk', 'isbn', 'mean_age')
self.assertEqual(list(books), [{
"isbn": "159059725",
"mean_age": 34.5,
}])
books = Book.objects.filter(isbn="159059725").annotate(
mean_age=Avg("authors__age")).values("name")
self.assertEqual(list(books), [{
"name":
"The Definitive Guide to Django: Web Development Done Right"
}])
books = Book.objects.filter(isbn="159059725").values().annotate(
mean_age=Avg('authors__age'))
self.assertEqual(list(books), [{
"contact_age": 34,
"contact_name": u'Adrian Holovaty',
"isbn": "159059725",
"mean_age": 34.5,
"name":
"The Definitive Guide to Django: Web Development Done Right",
"pages": 447,
"price": Approximate(Decimal("30")),
"pubdate": datetime.date(2007, 12, 6),
"publisher_id": 1,
"rating": 4.5,
}])
books = Book.objects.values("rating").annotate(
n_authors=Count("authors__id"),
mean_age=Avg("authors__age")).order_by("rating")
self.assertEqual(list(books), [{
"rating": 3.0,
"n_authors": 1,
"mean_age": 45.0,
}, {
"rating": 4.0,
"n_authors": 6,
"mean_age": Approximate(37.16, places=1)
}, {
"rating": 4.5,
"n_authors": 2,
"mean_age": 34.5,
}, {
"rating": 5.0,
"n_authors": 1,
"mean_age": 57.0,
}])
authors = Author.objects.annotate(Avg("friends__age")).order_by("name")
self.assertEqual(len(authors), 9)
self.assertQuerysetEqual(
authors, [(u'Adrian Holovaty', 32.0), (u'Brad Dayley', None),
(u'Jacob Kaplan-Moss', 29.5), (u'James Bennett', 34.0),
(u'Jeffrey Forcier', 27.0), (u'Paul Bissex', 31.0),
(u'Peter Norvig', 46.0), (u'Stuart Russell', 57.0),
(u'Wesley J. Chun', Approximate(33.66, places=1))],
lambda a: (a.name, a.friends__age__avg))
def test_not_reversibility(self):
self.assertEquals([], disassemble_pk(assemble_pk(None, '', 'TEST')))
def test_create_book_from_pk(self):
com_pk = assemble_pk("Libro sulle compositeKey", "Simone")
book = Book.objects.create(pk=com_pk)
self.assertIsNotNone(book)
book = Book.objects.get(pk=book.pk)
self.assertIsNotNone(book)
def test_not_reversibility(self):
self.assertEquals([], disassemble_pk(assemble_pk(None, '', 'TEST')))
def test_reversibility(self):
# '', None, NOT ammissible
params = ['ab', 'a'+SEP+'b', 'a'+ESCAPE_CHAR+SEP+'b', '123', 'a'+SEP, 'b'+ESCAPE_CHAR, 'c'+ESCAPE_CHAR+SEP, SEP, ESCAPE_CHAR, ESCAPE_CHAR+SEP, SEP+ESCAPE_CHAR, NONE_CHAR , 'd'+ESCAPE_CHAR+SEP]
self.assertEquals(params, disassemble_pk(assemble_pk(*params)))
def test_empty(self):
#self.assertEquals(None, assemble_pk(None))
self.assertEquals(None, assemble_pk(None))#NONE_CHAR
self.assertEquals('', assemble_pk(''))
self.assertEquals([], disassemble_pk(None))
self.assertEquals([''], disassemble_pk(''))
def test_pk(self):
self.assertEquals(['TEST'], disassemble_pk('TEST'))
self.assertEquals(['1', '2'], disassemble_pk(assemble_pk("1", "2")))
def test_create_book_from_pk(self):
com_pk = assemble_pk("Libro sulle compositeKey", "Simone")
book = Book.objects.create(pk=com_pk)
self.assertIsNotNone(book)
book = Book.objects.get(pk=book.pk)
self.assertIsNotNone(book)
def test_empty(self):
#self.assertEquals(None, assemble_pk(None))
self.assertEquals(None, assemble_pk(None)) #NONE_CHAR
self.assertEquals('', assemble_pk(''))
self.assertEquals([], disassemble_pk(None))
self.assertEquals([''], disassemble_pk(''))
def test_annotate_values(self):
books = list(Book.objects.filter(pk=assemble_pk(159059725, "The Definitive Guide to Django: Web Development Done Right")).annotate(mean_age=Avg("authors__age")).values())
self.assertEqual(
books, [
{
"contact_age": 34,
"contact_name": u'Adrian Holovaty',
"isbn": "159059725",
"mean_age": 34.5,
"name": "The Definitive Guide to Django: Web Development Done Right",
"pages": 447,
"price": Approximate(Decimal("30")),
"pubdate": datetime.date(2007, 12, 6),
"publisher_id": 1,
"rating": 4.5,
}
]
)
books = Book.objects.filter(pk=assemble_pk(159059725, "The Definitive Guide to Django: Web Development Done Right")).annotate(mean_age=Avg('authors__age')).values('pk', 'isbn', 'mean_age')
self.assertEqual(
list(books), [
{
"isbn": "159059725",
"mean_age": 34.5,
}
]
)
books = Book.objects.filter(isbn="159059725").annotate(mean_age=Avg("authors__age")).values("name")
self.assertEqual(
list(books), [
{
"name": "The Definitive Guide to Django: Web Development Done Right"
}
]
)
books = Book.objects.filter(isbn="159059725").values().annotate(mean_age=Avg('authors__age'))
self.assertEqual(
list(books), [
{
"contact_age": 34,
"contact_name": u'Adrian Holovaty',
"isbn": "159059725",
"mean_age": 34.5,
"name": "The Definitive Guide to Django: Web Development Done Right",
"pages": 447,
"price": Approximate(Decimal("30")),
"pubdate": datetime.date(2007, 12, 6),
"publisher_id": 1,
"rating": 4.5,
}
]
)
books = Book.objects.values("rating").annotate(n_authors=Count("authors__id"), mean_age=Avg("authors__age")).order_by("rating")
self.assertEqual(
list(books), [
{
"rating": 3.0,
"n_authors": 1,
"mean_age": 45.0,
},
{
"rating": 4.0,
"n_authors": 6,
"mean_age": Approximate(37.16, places=1)
},
{
"rating": 4.5,
"n_authors": 2,
"mean_age": 34.5,
},
{
"rating": 5.0,
"n_authors": 1,
"mean_age": 57.0,
}
]
)
authors = Author.objects.annotate(Avg("friends__age")).order_by("name")
self.assertEqual(len(authors), 9)
self.assertQuerysetEqual(
authors, [
(u'Adrian Holovaty', 32.0),
(u'Brad Dayley', None),
(u'Jacob Kaplan-Moss', 29.5),
(u'James Bennett', 34.0),
(u'Jeffrey Forcier', 27.0),
(u'Paul Bissex', 31.0),
(u'Peter Norvig', 46.0),
(u'Stuart Russell', 57.0),
(u'Wesley J. Chun', Approximate(33.66, places=1))
],
lambda a: (a.name, a.friends__age__avg)
)
def test_pk(self):
self.assertEquals(['TEST'], disassemble_pk('TEST'))
self.assertEquals(['1', '2'], disassemble_pk(assemble_pk("1", "2")))
