Debian Codegraph @

tests/raw_query/tests.py

raw
1 from datetime import date
2 from decimal import Decimal
3
4 from django.core.exceptions import FieldDoesNotExist
5 from django.db.models.query import RawQuerySet
6 from django.test import TestCase, skipUnlessDBFeature
7
8 from .models import (
9 Author,
10 Book,
11 BookFkAsPk,
12 Coffee,
13 FriendlyAuthor,
14 MixedCaseIDColumn,
15 Reviewer,
16 )
17
18
19 class RawQueryTests(TestCase):
20 @classmethod
21 def setUpTestData(cls):
22 cls.a1 = Author.objects.create(
23 first_name="Joe", last_name="Smith", dob=date(1950, 9, 20)
24 )
25 cls.a2 = Author.objects.create(
26 first_name="Jill", last_name="Doe", dob=date(1920, 4, 2)
27 )
28 cls.a3 = Author.objects.create(
29 first_name="Bob", last_name="Smith", dob=date(1986, 1, 25)
30 )
31 cls.a4 = Author.objects.create(
32 first_name="Bill", last_name="Jones", dob=date(1932, 5, 10)
33 )
34 cls.b1 = Book.objects.create(
35 title="The awesome book",
36 author=cls.a1,
37 paperback=False,
38 opening_line=(
39 "It was a bright cold day in April and the clocks were striking "
40 "thirteen."
41 ),
42 )
43 cls.b2 = Book.objects.create(
44 title="The horrible book",
45 author=cls.a1,
46 paperback=True,
47 opening_line=(
48 "On an evening in the latter part of May a middle-aged man "
49 "was walking homeward from Shaston to the village of Marlott, "
50 "in the adjoining Vale of Blakemore, or Blackmoor."
51 ),
52 )
53 cls.b3 = Book.objects.create(
54 title="Another awesome book",
55 author=cls.a1,
56 paperback=False,
57 opening_line="A squat gray building of only thirty-four stories.",
58 )
59 cls.b4 = Book.objects.create(
60 title="Some other book",
61 author=cls.a3,
62 paperback=True,
63 opening_line="It was the day my grandmother exploded.",
64 )
65 cls.c1 = Coffee.objects.create(brand="dunkin doughnuts")
66 cls.c2 = Coffee.objects.create(brand="starbucks")
67 cls.r1 = Reviewer.objects.create()
68 cls.r2 = Reviewer.objects.create()
69 cls.r1.reviewed.add(cls.b2, cls.b3, cls.b4)
70
71 def assertSuccessfulRawQuery(
72 self,
73 model,
74 query,
75 expected_results,
76 expected_annotations=(),
77 params=[],
78 translations=None,
79 ):
80 """
81 Execute the passed query against the passed model and check the output
82 """
83 results = list(
84 model.objects.raw(query, params=params, translations=translations)
85 )
86 self.assertProcessed(model, results, expected_results, expected_annotations)
87 self.assertAnnotations(results, expected_annotations)
88
89 def assertProcessed(self, model, results, orig, expected_annotations=()):
90 """
91 Compare the results of a raw query against expected results
92 """
93 self.assertEqual(len(results), len(orig))
94 for index, item in enumerate(results):
95 orig_item = orig[index]
96 for annotation in expected_annotations:
97 setattr(orig_item, *annotation)
98
99 for field in model._meta.fields:
100 # All values on the model are equal
101 self.assertEqual(
102 getattr(item, field.attname), getattr(orig_item, field.attname)
103 )
104 # This includes checking that they are the same type
105 self.assertEqual(
106 type(getattr(item, field.attname)),
107 type(getattr(orig_item, field.attname)),
108 )
109
110 def assertNoAnnotations(self, results):
111 """
112 The results of a raw query contain no annotations
113 """
114 self.assertAnnotations(results, ())
115
116 def assertAnnotations(self, results, expected_annotations):
117 """
118 The passed raw query results contain the expected annotations
119 """
120 if expected_annotations:
121 for index, result in enumerate(results):
122 annotation, value = expected_annotations[index]
123 self.assertTrue(hasattr(result, annotation))
124 self.assertEqual(getattr(result, annotation), value)
125
126 def test_rawqueryset_repr(self):
127 queryset = RawQuerySet(raw_query="SELECT * FROM raw_query_author")
128 self.assertEqual(
129 repr(queryset), "<RawQuerySet: SELECT * FROM raw_query_author>"
130 )
131 self.assertEqual(
132 repr(queryset.query), "<RawQuery: SELECT * FROM raw_query_author>"
133 )
134
135 def test_simple_raw_query(self):
136 """
137 Basic test of raw query with a simple database query
138 """
139 query = "SELECT * FROM raw_query_author"
140 authors = Author.objects.all()
141 self.assertSuccessfulRawQuery(Author, query, authors)
142
143 def test_raw_query_lazy(self):
144 """
145 Raw queries are lazy: they aren't actually executed until they're
146 iterated over.
147 """
148 q = Author.objects.raw("SELECT * FROM raw_query_author")
149 self.assertIsNone(q.query.cursor)
150 list(q)
151 self.assertIsNotNone(q.query.cursor)
152
153 def test_FK_raw_query(self):
154 """
155 Test of a simple raw query against a model containing a foreign key
156 """
157 query = "SELECT * FROM raw_query_book"
158 books = Book.objects.all()
159 self.assertSuccessfulRawQuery(Book, query, books)
160
161 def test_db_column_handler(self):
162 """
163 Test of a simple raw query against a model containing a field with
164 db_column defined.
165 """
166 query = "SELECT * FROM raw_query_coffee"
167 coffees = Coffee.objects.all()
168 self.assertSuccessfulRawQuery(Coffee, query, coffees)
169
170 def test_pk_with_mixed_case_db_column(self):
171 """
172 A raw query with a model that has a pk db_column with mixed case.
173 """
174 query = "SELECT * FROM raw_query_mixedcaseidcolumn"
175 queryset = MixedCaseIDColumn.objects.all()
176 self.assertSuccessfulRawQuery(MixedCaseIDColumn, query, queryset)
177
178 def test_order_handler(self):
179 """Raw query tolerates columns being returned in any order."""
180 selects = (
181 ("dob, last_name, first_name, id"),
182 ("last_name, dob, first_name, id"),
183 ("first_name, last_name, dob, id"),
184 )
185
186 for select in selects:
187 query = "SELECT %s FROM raw_query_author" % select
188 authors = Author.objects.all()
189 self.assertSuccessfulRawQuery(Author, query, authors)
190
191 def test_translations(self):
192 """
193 Test of raw query's optional ability to translate unexpected result
194 column names to specific model fields
195 """
196 query = (
197 "SELECT first_name AS first, last_name AS last, dob, id "
198 "FROM raw_query_author"
199 )
200 translations = {"first": "first_name", "last": "last_name"}
201 authors = Author.objects.all()
202 self.assertSuccessfulRawQuery(Author, query, authors, translations=translations)
203
204 def test_params(self):
205 """
206 Test passing optional query parameters
207 """
208 query = "SELECT * FROM raw_query_author WHERE first_name = %s"
209 author = Author.objects.all()[2]
210 params = [author.first_name]
211 qset = Author.objects.raw(query, params=params)
212 results = list(qset)
213 self.assertProcessed(Author, results, [author])
214 self.assertNoAnnotations(results)
215 self.assertEqual(len(results), 1)
216 self.assertIsInstance(repr(qset), str)
217
218 def test_params_none(self):
219 query = "SELECT * FROM raw_query_author WHERE first_name like 'J%'"
220 qset = Author.objects.raw(query, params=None)
221 self.assertEqual(len(qset), 2)
222
223 def test_escaped_percent(self):
224 query = "SELECT * FROM raw_query_author WHERE first_name like 'J%%'"
225 qset = Author.objects.raw(query)
226 self.assertEqual(len(qset), 2)
227
228 @skipUnlessDBFeature("supports_paramstyle_pyformat")
229 def test_pyformat_params(self):
230 """
231 Test passing optional query parameters
232 """
233 query = "SELECT * FROM raw_query_author WHERE first_name = %(first)s"
234 author = Author.objects.all()[2]
235 params = {"first": author.first_name}
236 qset = Author.objects.raw(query, params=params)
237 results = list(qset)
238 self.assertProcessed(Author, results, [author])
239 self.assertNoAnnotations(results)
240 self.assertEqual(len(results), 1)
241 self.assertIsInstance(repr(qset), str)
242
243 def test_query_representation(self):
244 """
245 Test representation of raw query with parameters
246 """
247 query = "SELECT * FROM raw_query_author WHERE last_name = %(last)s"
248 qset = Author.objects.raw(query, {"last": "foo"})
249 self.assertEqual(
250 repr(qset),
251 "<RawQuerySet: SELECT * FROM raw_query_author WHERE last_name = foo>",
252 )
253 self.assertEqual(
254 repr(qset.query),
255 "<RawQuery: SELECT * FROM raw_query_author WHERE last_name = foo>",
256 )
257
258 query = "SELECT * FROM raw_query_author WHERE last_name = %s"
259 qset = Author.objects.raw(query, {"foo"})
260 self.assertEqual(
261 repr(qset),
262 "<RawQuerySet: SELECT * FROM raw_query_author WHERE last_name = foo>",
263 )
264 self.assertEqual(
265 repr(qset.query),
266 "<RawQuery: SELECT * FROM raw_query_author WHERE last_name = foo>",
267 )
268
269 def test_many_to_many(self):
270 """
271 Test of a simple raw query against a model containing a m2m field
272 """
273 query = "SELECT * FROM raw_query_reviewer"
274 reviewers = Reviewer.objects.all()
275 self.assertSuccessfulRawQuery(Reviewer, query, reviewers)
276
277 def test_extra_conversions(self):
278 """Extra translations are ignored."""
279 query = "SELECT * FROM raw_query_author"
280 translations = {"something": "else"}
281 authors = Author.objects.all()
282 self.assertSuccessfulRawQuery(Author, query, authors, translations=translations)
283
284 def test_missing_fields(self):
285 query = "SELECT id, first_name, dob FROM raw_query_author"
286 for author in Author.objects.raw(query):
287 self.assertIsNotNone(author.first_name)
288 # last_name isn't given, but it will be retrieved on demand
289 self.assertIsNotNone(author.last_name)
290
291 def test_missing_fields_without_PK(self):
292 query = "SELECT first_name, dob FROM raw_query_author"
293 msg = "Raw query must include the primary key"
294 with self.assertRaisesMessage(FieldDoesNotExist, msg):
295 list(Author.objects.raw(query))
296
297 def test_annotations(self):
298 query = (
299 "SELECT a.*, count(b.id) as book_count "
300 "FROM raw_query_author a "
301 "LEFT JOIN raw_query_book b ON a.id = b.author_id "
302 "GROUP BY a.id, a.first_name, a.last_name, a.dob ORDER BY a.id"
303 )
304 expected_annotations = (
305 ("book_count", 3),
306 ("book_count", 0),
307 ("book_count", 1),
308 ("book_count", 0),
309 )
310 authors = Author.objects.order_by("pk")
311 self.assertSuccessfulRawQuery(Author, query, authors, expected_annotations)
312
313 def test_white_space_query(self):
314 query = " SELECT * FROM raw_query_author"
315 authors = Author.objects.all()
316 self.assertSuccessfulRawQuery(Author, query, authors)
317
318 def test_multiple_iterations(self):
319 query = "SELECT * FROM raw_query_author"
320 normal_authors = Author.objects.all()
321 raw_authors = Author.objects.raw(query)
322
323 # First Iteration
324 first_iterations = 0
325 for index, raw_author in enumerate(raw_authors):
326 self.assertEqual(normal_authors[index], raw_author)
327 first_iterations += 1
328
329 # Second Iteration
330 second_iterations = 0
331 for index, raw_author in enumerate(raw_authors):
332 self.assertEqual(normal_authors[index], raw_author)
333 second_iterations += 1
334
335 self.assertEqual(first_iterations, second_iterations)
336
337 def test_get_item(self):
338 # Indexing on RawQuerySets
339 query = "SELECT * FROM raw_query_author ORDER BY id ASC"
340 third_author = Author.objects.raw(query)[2]
341 self.assertEqual(third_author.first_name, "Bob")
342
343 first_two = Author.objects.raw(query)[0:2]
344 self.assertEqual(len(first_two), 2)
345
346 with self.assertRaises(TypeError):
347 Author.objects.raw(query)["test"]
348
349 def test_inheritance(self):
350 f = FriendlyAuthor.objects.create(
351 first_name="Wesley", last_name="Chun", dob=date(1962, 10, 28)
352 )
353 query = "SELECT * FROM raw_query_friendlyauthor"
354 self.assertEqual([o.pk for o in FriendlyAuthor.objects.raw(query)], [f.pk])
355
356 def test_query_count(self):
357 self.assertNumQueries(
358 1, list, Author.objects.raw("SELECT * FROM raw_query_author")
359 )
360
361 def test_subquery_in_raw_sql(self):
362 list(
363 Book.objects.raw(
364 "SELECT id FROM "
365 "(SELECT * FROM raw_query_book WHERE paperback IS NOT NULL) sq"
366 )
367 )
368
369 def test_db_column_name_is_used_in_raw_query(self):
370 """
371 Regression test that ensures the `column` attribute on the field is
372 used to generate the list of fields included in the query, as opposed
373 to the `attname`. This is important when the primary key is a
374 ForeignKey field because `attname` and `column` are not necessarily the
375 same.
376 """
377 b = BookFkAsPk.objects.create(book=self.b1)
378 self.assertEqual(
379 list(
380 BookFkAsPk.objects.raw(
381 "SELECT not_the_default FROM raw_query_bookfkaspk"
382 )
383 ),
384 [b],
385 )
386
387 def test_decimal_parameter(self):
388 c = Coffee.objects.create(brand="starbucks", price=20.5)
389 qs = Coffee.objects.raw(
390 "SELECT * FROM raw_query_coffee WHERE price >= %s", params=[Decimal(20)]
391 )
392 self.assertEqual(list(qs), [c])
393
394 def test_result_caching(self):
395 with self.assertNumQueries(1):
396 books = Book.objects.raw("SELECT * FROM raw_query_book")
397 list(books)
398 list(books)
399
400 def test_iterator(self):
401 with self.assertNumQueries(2):
402 books = Book.objects.raw("SELECT * FROM raw_query_book")
403 list(books.iterator())
404 list(books.iterator())
405
406 def test_bool(self):
407 self.assertIs(bool(Book.objects.raw("SELECT * FROM raw_query_book")), True)
408 self.assertIs(
409 bool(Book.objects.raw("SELECT * FROM raw_query_book WHERE id = 0")), False
410 )
411
412 def test_len(self):
413 self.assertEqual(len(Book.objects.raw("SELECT * FROM raw_query_book")), 4)
414 self.assertEqual(
415 len(Book.objects.raw("SELECT * FROM raw_query_book WHERE id = 0")), 0
416 )