/tests/modeltests/raw_query/tests.py
Python | 220 lines | 187 code | 11 blank | 22 comment | 11 complexity | d617bb649a8cd27312922d05a4ebeed2 MD5 | raw file
1from datetime import date 2 3from django.db.models.sql.query import InvalidQuery 4from django.test import TestCase 5 6from models import Author, Book, Coffee, Reviewer, FriendlyAuthor 7 8 9class RawQueryTests(TestCase): 10 fixtures = ['raw_query_books.json'] 11 12 def assertSuccessfulRawQuery(self, model, query, expected_results, 13 expected_annotations=(), params=[], translations=None): 14 """ 15 Execute the passed query against the passed model and check the output 16 """ 17 results = list(model.objects.raw(query, params=params, translations=translations)) 18 self.assertProcessed(model, results, expected_results, expected_annotations) 19 self.assertAnnotations(results, expected_annotations) 20 21 def assertProcessed(self, model, results, orig, expected_annotations=()): 22 """ 23 Compare the results of a raw query against expected results 24 """ 25 self.assertEqual(len(results), len(orig)) 26 for index, item in enumerate(results): 27 orig_item = orig[index] 28 for annotation in expected_annotations: 29 setattr(orig_item, *annotation) 30 31 for field in model._meta.fields: 32 # Check that all values on the model are equal 33 self.assertEqual(getattr(item,field.attname), 34 getattr(orig_item,field.attname)) 35 # This includes checking that they are the same type 36 self.assertEqual(type(getattr(item,field.attname)), 37 type(getattr(orig_item,field.attname))) 38 39 def assertNoAnnotations(self, results): 40 """ 41 Check that the results of a raw query contain no annotations 42 """ 43 self.assertAnnotations(results, ()) 44 45 def assertAnnotations(self, results, expected_annotations): 46 """ 47 Check that the passed raw query results contain the expected 48 annotations 49 """ 50 if expected_annotations: 51 for index, result in enumerate(results): 52 annotation, value = expected_annotations[index] 53 self.assertTrue(hasattr(result, annotation)) 54 self.assertEqual(getattr(result, annotation), value) 55 56 def testSimpleRawQuery(self): 57 """ 58 Basic test of raw query with a simple database query 59 """ 60 query = "SELECT * FROM raw_query_author" 61 authors = Author.objects.all() 62 self.assertSuccessfulRawQuery(Author, query, authors) 63 64 def testRawQueryLazy(self): 65 """ 66 Raw queries are lazy: they aren't actually executed until they're 67 iterated over. 68 """ 69 q = Author.objects.raw('SELECT * FROM raw_query_author') 70 self.assertTrue(q.query.cursor is None) 71 list(q) 72 self.assertTrue(q.query.cursor is not None) 73 74 def testFkeyRawQuery(self): 75 """ 76 Test of a simple raw query against a model containing a foreign key 77 """ 78 query = "SELECT * FROM raw_query_book" 79 books = Book.objects.all() 80 self.assertSuccessfulRawQuery(Book, query, books) 81 82 def testDBColumnHandler(self): 83 """ 84 Test of a simple raw query against a model containing a field with 85 db_column defined. 86 """ 87 query = "SELECT * FROM raw_query_coffee" 88 coffees = Coffee.objects.all() 89 self.assertSuccessfulRawQuery(Coffee, query, coffees) 90 91 def testOrderHandler(self): 92 """ 93 Test of raw raw query's tolerance for columns being returned in any 94 order 95 """ 96 selects = ( 97 ('dob, last_name, first_name, id'), 98 ('last_name, dob, first_name, id'), 99 ('first_name, last_name, dob, id'), 100 ) 101 102 for select in selects: 103 query = "SELECT %s FROM raw_query_author" % select 104 authors = Author.objects.all() 105 self.assertSuccessfulRawQuery(Author, query, authors) 106 107 def testTranslations(self): 108 """ 109 Test of raw query's optional ability to translate unexpected result 110 column names to specific model fields 111 """ 112 query = "SELECT first_name AS first, last_name AS last, dob, id FROM raw_query_author" 113 translations = {'first': 'first_name', 'last': 'last_name'} 114 authors = Author.objects.all() 115 self.assertSuccessfulRawQuery(Author, query, authors, translations=translations) 116 117 def testParams(self): 118 """ 119 Test passing optional query parameters 120 """ 121 query = "SELECT * FROM raw_query_author WHERE first_name = %s" 122 author = Author.objects.all()[2] 123 params = [author.first_name] 124 results = list(Author.objects.raw(query, params=params)) 125 self.assertProcessed(Author, results, [author]) 126 self.assertNoAnnotations(results) 127 self.assertEqual(len(results), 1) 128 129 def testManyToMany(self): 130 """ 131 Test of a simple raw query against a model containing a m2m field 132 """ 133 query = "SELECT * FROM raw_query_reviewer" 134 reviewers = Reviewer.objects.all() 135 self.assertSuccessfulRawQuery(Reviewer, query, reviewers) 136 137 def testExtraConversions(self): 138 """ 139 Test to insure that extra translations are ignored. 140 """ 141 query = "SELECT * FROM raw_query_author" 142 translations = {'something': 'else'} 143 authors = Author.objects.all() 144 self.assertSuccessfulRawQuery(Author, query, authors, translations=translations) 145 146 def testMissingFields(self): 147 query = "SELECT id, first_name, dob FROM raw_query_author" 148 for author in Author.objects.raw(query): 149 self.assertNotEqual(author.first_name, None) 150 # last_name isn't given, but it will be retrieved on demand 151 self.assertNotEqual(author.last_name, None) 152 153 def testMissingFieldsWithoutPK(self): 154 query = "SELECT first_name, dob FROM raw_query_author" 155 try: 156 list(Author.objects.raw(query)) 157 self.fail('Query without primary key should fail') 158 except InvalidQuery: 159 pass 160 161 def testAnnotations(self): 162 query = "SELECT a.*, count(b.id) as book_count FROM raw_query_author a LEFT JOIN raw_query_book b ON a.id = b.author_id GROUP BY a.id, a.first_name, a.last_name, a.dob ORDER BY a.id" 163 expected_annotations = ( 164 ('book_count', 3), 165 ('book_count', 0), 166 ('book_count', 1), 167 ('book_count', 0), 168 ) 169 authors = Author.objects.all() 170 self.assertSuccessfulRawQuery(Author, query, authors, expected_annotations) 171 172 def testWhiteSpaceQuery(self): 173 query = " SELECT * FROM raw_query_author" 174 authors = Author.objects.all() 175 self.assertSuccessfulRawQuery(Author, query, authors) 176 177 def testMultipleIterations(self): 178 query = "SELECT * FROM raw_query_author" 179 normal_authors = Author.objects.all() 180 raw_authors = Author.objects.raw(query) 181 182 # First Iteration 183 first_iterations = 0 184 for index, raw_author in enumerate(raw_authors): 185 self.assertEqual(normal_authors[index], raw_author) 186 first_iterations += 1 187 188 # Second Iteration 189 second_iterations = 0 190 for index, raw_author in enumerate(raw_authors): 191 self.assertEqual(normal_authors[index], raw_author) 192 second_iterations += 1 193 194 self.assertEqual(first_iterations, second_iterations) 195 196 def testGetItem(self): 197 # Indexing on RawQuerySets 198 query = "SELECT * FROM raw_query_author ORDER BY id ASC" 199 third_author = Author.objects.raw(query)[2] 200 self.assertEqual(third_author.first_name, 'Bob') 201 202 first_two = Author.objects.raw(query)[0:2] 203 self.assertEqual(len(first_two), 2) 204 205 self.assertRaises(TypeError, lambda: Author.objects.raw(query)['test']) 206 207 def test_inheritance(self): 208 # date is the end of the Cuban Missile Crisis, I have no idea when 209 # Wesley was bron 210 f = FriendlyAuthor.objects.create(first_name="Wesley", last_name="Chun", 211 dob=date(1962, 10, 28)) 212 query = "SELECT * FROM raw_query_friendlyauthor" 213 self.assertEqual( 214 [o.pk for o in FriendlyAuthor.objects.raw(query)], [f.pk] 215 ) 216 217 def test_query_count(self): 218 self.assertNumQueries(1, 219 list, Author.objects.raw("SELECT * FROM raw_query_author") 220 )