/tests/modeltests/raw_query/tests.py

  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        )