|
1 """ |
|
2 Query subclasses which provide extra functionality beyond simple data retrieval. |
|
3 """ |
|
4 |
|
5 from django.contrib.contenttypes import generic |
|
6 from django.core.exceptions import FieldError |
|
7 from django.db.models.sql.constants import * |
|
8 from django.db.models.sql.datastructures import RawValue, Date |
|
9 from django.db.models.sql.query import Query |
|
10 from django.db.models.sql.where import AND |
|
11 |
|
12 __all__ = ['DeleteQuery', 'UpdateQuery', 'InsertQuery', 'DateQuery', |
|
13 'CountQuery'] |
|
14 |
|
15 class DeleteQuery(Query): |
|
16 """ |
|
17 Delete queries are done through this class, since they are more constrained |
|
18 than general queries. |
|
19 """ |
|
20 def as_sql(self): |
|
21 """ |
|
22 Creates the SQL for this query. Returns the SQL string and list of |
|
23 parameters. |
|
24 """ |
|
25 assert len(self.tables) == 1, \ |
|
26 "Can only delete from one table at a time." |
|
27 result = ['DELETE FROM %s' % self.quote_name_unless_alias(self.tables[0])] |
|
28 where, params = self.where.as_sql() |
|
29 result.append('WHERE %s' % where) |
|
30 return ' '.join(result), tuple(params) |
|
31 |
|
32 def do_query(self, table, where): |
|
33 self.tables = [table] |
|
34 self.where = where |
|
35 self.execute_sql(None) |
|
36 |
|
37 def delete_batch_related(self, pk_list): |
|
38 """ |
|
39 Set up and execute delete queries for all the objects related to the |
|
40 primary key values in pk_list. To delete the objects themselves, use |
|
41 the delete_batch() method. |
|
42 |
|
43 More than one physical query may be executed if there are a |
|
44 lot of values in pk_list. |
|
45 """ |
|
46 cls = self.model |
|
47 for related in cls._meta.get_all_related_many_to_many_objects(): |
|
48 if not isinstance(related.field, generic.GenericRelation): |
|
49 for offset in range(0, len(pk_list), GET_ITERATOR_CHUNK_SIZE): |
|
50 where = self.where_class() |
|
51 where.add((None, related.field.m2m_reverse_name(), |
|
52 related.field, 'in', |
|
53 pk_list[offset : offset+GET_ITERATOR_CHUNK_SIZE]), |
|
54 AND) |
|
55 self.do_query(related.field.m2m_db_table(), where) |
|
56 |
|
57 for f in cls._meta.many_to_many: |
|
58 w1 = self.where_class() |
|
59 if isinstance(f, generic.GenericRelation): |
|
60 from django.contrib.contenttypes.models import ContentType |
|
61 field = f.rel.to._meta.get_field(f.content_type_field_name) |
|
62 w1.add((None, field.column, field, 'exact', |
|
63 ContentType.objects.get_for_model(cls).id), AND) |
|
64 for offset in range(0, len(pk_list), GET_ITERATOR_CHUNK_SIZE): |
|
65 where = self.where_class() |
|
66 where.add((None, f.m2m_column_name(), f, 'in', |
|
67 pk_list[offset : offset + GET_ITERATOR_CHUNK_SIZE]), |
|
68 AND) |
|
69 if w1: |
|
70 where.add(w1, AND) |
|
71 self.do_query(f.m2m_db_table(), where) |
|
72 |
|
73 def delete_batch(self, pk_list): |
|
74 """ |
|
75 Set up and execute delete queries for all the objects in pk_list. This |
|
76 should be called after delete_batch_related(), if necessary. |
|
77 |
|
78 More than one physical query may be executed if there are a |
|
79 lot of values in pk_list. |
|
80 """ |
|
81 for offset in range(0, len(pk_list), GET_ITERATOR_CHUNK_SIZE): |
|
82 where = self.where_class() |
|
83 field = self.model._meta.pk |
|
84 where.add((None, field.column, field, 'in', |
|
85 pk_list[offset : offset + GET_ITERATOR_CHUNK_SIZE]), AND) |
|
86 self.do_query(self.model._meta.db_table, where) |
|
87 |
|
88 class UpdateQuery(Query): |
|
89 """ |
|
90 Represents an "update" SQL query. |
|
91 """ |
|
92 def __init__(self, *args, **kwargs): |
|
93 super(UpdateQuery, self).__init__(*args, **kwargs) |
|
94 self._setup_query() |
|
95 |
|
96 def _setup_query(self): |
|
97 """ |
|
98 Runs on initialization and after cloning. Any attributes that would |
|
99 normally be set in __init__ should go in here, instead, so that they |
|
100 are also set up after a clone() call. |
|
101 """ |
|
102 self.values = [] |
|
103 self.related_ids = None |
|
104 if not hasattr(self, 'related_updates'): |
|
105 self.related_updates = {} |
|
106 |
|
107 def clone(self, klass=None, **kwargs): |
|
108 return super(UpdateQuery, self).clone(klass, |
|
109 related_updates=self.related_updates.copy, **kwargs) |
|
110 |
|
111 def execute_sql(self, result_type=None): |
|
112 super(UpdateQuery, self).execute_sql(result_type) |
|
113 for query in self.get_related_updates(): |
|
114 query.execute_sql(result_type) |
|
115 |
|
116 def as_sql(self): |
|
117 """ |
|
118 Creates the SQL for this query. Returns the SQL string and list of |
|
119 parameters. |
|
120 """ |
|
121 self.pre_sql_setup() |
|
122 if not self.values: |
|
123 return '', () |
|
124 table = self.tables[0] |
|
125 qn = self.quote_name_unless_alias |
|
126 result = ['UPDATE %s' % qn(table)] |
|
127 result.append('SET') |
|
128 values, update_params = [], [] |
|
129 for name, val, placeholder in self.values: |
|
130 if val is not None: |
|
131 values.append('%s = %s' % (qn(name), placeholder)) |
|
132 update_params.append(val) |
|
133 else: |
|
134 values.append('%s = NULL' % qn(name)) |
|
135 result.append(', '.join(values)) |
|
136 where, params = self.where.as_sql() |
|
137 if where: |
|
138 result.append('WHERE %s' % where) |
|
139 return ' '.join(result), tuple(update_params + params) |
|
140 |
|
141 def pre_sql_setup(self): |
|
142 """ |
|
143 If the update depends on results from other tables, we need to do some |
|
144 munging of the "where" conditions to match the format required for |
|
145 (portable) SQL updates. That is done here. |
|
146 |
|
147 Further, if we are going to be running multiple updates, we pull out |
|
148 the id values to update at this point so that they don't change as a |
|
149 result of the progressive updates. |
|
150 """ |
|
151 self.select_related = False |
|
152 self.clear_ordering(True) |
|
153 super(UpdateQuery, self).pre_sql_setup() |
|
154 count = self.count_active_tables() |
|
155 if not self.related_updates and count == 1: |
|
156 return |
|
157 |
|
158 # We need to use a sub-select in the where clause to filter on things |
|
159 # from other tables. |
|
160 query = self.clone(klass=Query) |
|
161 query.bump_prefix() |
|
162 query.extra_select = {} |
|
163 first_table = query.tables[0] |
|
164 if query.alias_refcount[first_table] == 1: |
|
165 # We can remove one table from the inner query. |
|
166 query.unref_alias(first_table) |
|
167 for i in xrange(1, len(query.tables)): |
|
168 table = query.tables[i] |
|
169 if query.alias_refcount[table]: |
|
170 break |
|
171 join_info = query.alias_map[table] |
|
172 query.select = [(join_info[RHS_ALIAS], join_info[RHS_JOIN_COL])] |
|
173 must_pre_select = False |
|
174 else: |
|
175 query.select = [] |
|
176 query.add_fields([query.model._meta.pk.name]) |
|
177 must_pre_select = not self.connection.features.update_can_self_select |
|
178 |
|
179 # Now we adjust the current query: reset the where clause and get rid |
|
180 # of all the tables we don't need (since they're in the sub-select). |
|
181 self.where = self.where_class() |
|
182 if self.related_updates or must_pre_select: |
|
183 # Either we're using the idents in multiple update queries (so |
|
184 # don't want them to change), or the db backend doesn't support |
|
185 # selecting from the updating table (e.g. MySQL). |
|
186 idents = [] |
|
187 for rows in query.execute_sql(MULTI): |
|
188 idents.extend([r[0] for r in rows]) |
|
189 self.add_filter(('pk__in', idents)) |
|
190 self.related_ids = idents |
|
191 else: |
|
192 # The fast path. Filters and updates in one query. |
|
193 self.add_filter(('pk__in', query)) |
|
194 for alias in self.tables[1:]: |
|
195 self.alias_refcount[alias] = 0 |
|
196 |
|
197 def clear_related(self, related_field, pk_list): |
|
198 """ |
|
199 Set up and execute an update query that clears related entries for the |
|
200 keys in pk_list. |
|
201 |
|
202 This is used by the QuerySet.delete_objects() method. |
|
203 """ |
|
204 for offset in range(0, len(pk_list), GET_ITERATOR_CHUNK_SIZE): |
|
205 self.where = self.where_class() |
|
206 f = self.model._meta.pk |
|
207 self.where.add((None, f.column, f, 'in', |
|
208 pk_list[offset : offset + GET_ITERATOR_CHUNK_SIZE]), |
|
209 AND) |
|
210 self.values = [(related_field.column, None, '%s')] |
|
211 self.execute_sql(None) |
|
212 |
|
213 def add_update_values(self, values): |
|
214 """ |
|
215 Convert a dictionary of field name to value mappings into an update |
|
216 query. This is the entry point for the public update() method on |
|
217 querysets. |
|
218 """ |
|
219 values_seq = [] |
|
220 for name, val in values.iteritems(): |
|
221 field, model, direct, m2m = self.model._meta.get_field_by_name(name) |
|
222 if not direct or m2m: |
|
223 raise FieldError('Cannot update model field %r (only non-relations and foreign keys permitted).' % field) |
|
224 values_seq.append((field, model, val)) |
|
225 return self.add_update_fields(values_seq) |
|
226 |
|
227 def add_update_fields(self, values_seq): |
|
228 """ |
|
229 Turn a sequence of (field, model, value) triples into an update query. |
|
230 Used by add_update_values() as well as the "fast" update path when |
|
231 saving models. |
|
232 """ |
|
233 from django.db.models.base import Model |
|
234 for field, model, val in values_seq: |
|
235 # FIXME: Some sort of db_prep_* is probably more appropriate here. |
|
236 if field.rel and isinstance(val, Model): |
|
237 val = val.pk |
|
238 |
|
239 # Getting the placeholder for the field. |
|
240 if hasattr(field, 'get_placeholder'): |
|
241 placeholder = field.get_placeholder(val) |
|
242 else: |
|
243 placeholder = '%s' |
|
244 |
|
245 if model: |
|
246 self.add_related_update(model, field.column, val, placeholder) |
|
247 else: |
|
248 self.values.append((field.column, val, placeholder)) |
|
249 |
|
250 def add_related_update(self, model, column, value, placeholder): |
|
251 """ |
|
252 Adds (name, value) to an update query for an ancestor model. |
|
253 |
|
254 Updates are coalesced so that we only run one update query per ancestor. |
|
255 """ |
|
256 try: |
|
257 self.related_updates[model].append((column, value, placeholder)) |
|
258 except KeyError: |
|
259 self.related_updates[model] = [(column, value, placeholder)] |
|
260 |
|
261 def get_related_updates(self): |
|
262 """ |
|
263 Returns a list of query objects: one for each update required to an |
|
264 ancestor model. Each query will have the same filtering conditions as |
|
265 the current query but will only update a single table. |
|
266 """ |
|
267 if not self.related_updates: |
|
268 return [] |
|
269 result = [] |
|
270 for model, values in self.related_updates.iteritems(): |
|
271 query = UpdateQuery(model, self.connection) |
|
272 query.values = values |
|
273 if self.related_ids: |
|
274 query.add_filter(('pk__in', self.related_ids)) |
|
275 result.append(query) |
|
276 return result |
|
277 |
|
278 class InsertQuery(Query): |
|
279 def __init__(self, *args, **kwargs): |
|
280 super(InsertQuery, self).__init__(*args, **kwargs) |
|
281 self.columns = [] |
|
282 self.values = [] |
|
283 self.params = () |
|
284 |
|
285 def clone(self, klass=None, **kwargs): |
|
286 extras = {'columns': self.columns[:], 'values': self.values[:], |
|
287 'params': self.params} |
|
288 return super(InsertQuery, self).clone(klass, extras) |
|
289 |
|
290 def as_sql(self): |
|
291 # We don't need quote_name_unless_alias() here, since these are all |
|
292 # going to be column names (so we can avoid the extra overhead). |
|
293 qn = self.connection.ops.quote_name |
|
294 result = ['INSERT INTO %s' % qn(self.model._meta.db_table)] |
|
295 result.append('(%s)' % ', '.join([qn(c) for c in self.columns])) |
|
296 result.append('VALUES (%s)' % ', '.join(self.values)) |
|
297 return ' '.join(result), self.params |
|
298 |
|
299 def execute_sql(self, return_id=False): |
|
300 cursor = super(InsertQuery, self).execute_sql(None) |
|
301 if return_id: |
|
302 return self.connection.ops.last_insert_id(cursor, |
|
303 self.model._meta.db_table, self.model._meta.pk.column) |
|
304 |
|
305 def insert_values(self, insert_values, raw_values=False): |
|
306 """ |
|
307 Set up the insert query from the 'insert_values' dictionary. The |
|
308 dictionary gives the model field names and their target values. |
|
309 |
|
310 If 'raw_values' is True, the values in the 'insert_values' dictionary |
|
311 are inserted directly into the query, rather than passed as SQL |
|
312 parameters. This provides a way to insert NULL and DEFAULT keywords |
|
313 into the query, for example. |
|
314 """ |
|
315 placeholders, values = [], [] |
|
316 for field, val in insert_values: |
|
317 if hasattr(field, 'get_placeholder'): |
|
318 # Some fields (e.g. geo fields) need special munging before |
|
319 # they can be inserted. |
|
320 placeholders.append(field.get_placeholder(val)) |
|
321 else: |
|
322 placeholders.append('%s') |
|
323 |
|
324 self.columns.append(field.column) |
|
325 values.append(val) |
|
326 if raw_values: |
|
327 self.values.extend(values) |
|
328 else: |
|
329 self.params += tuple(values) |
|
330 self.values.extend(placeholders) |
|
331 |
|
332 class DateQuery(Query): |
|
333 """ |
|
334 A DateQuery is a normal query, except that it specifically selects a single |
|
335 date field. This requires some special handling when converting the results |
|
336 back to Python objects, so we put it in a separate class. |
|
337 """ |
|
338 def results_iter(self): |
|
339 """ |
|
340 Returns an iterator over the results from executing this query. |
|
341 """ |
|
342 resolve_columns = hasattr(self, 'resolve_columns') |
|
343 if resolve_columns: |
|
344 from django.db.models.fields import DateTimeField |
|
345 fields = [DateTimeField()] |
|
346 else: |
|
347 from django.db.backends.util import typecast_timestamp |
|
348 needs_string_cast = self.connection.features.needs_datetime_string_cast |
|
349 |
|
350 offset = len(self.extra_select) |
|
351 for rows in self.execute_sql(MULTI): |
|
352 for row in rows: |
|
353 date = row[offset] |
|
354 if resolve_columns: |
|
355 date = self.resolve_columns([date], fields)[0] |
|
356 elif needs_string_cast: |
|
357 date = typecast_timestamp(str(date)) |
|
358 yield date |
|
359 |
|
360 def add_date_select(self, column, lookup_type, order='ASC'): |
|
361 """ |
|
362 Converts the query into a date extraction query. |
|
363 """ |
|
364 alias = self.join((None, self.model._meta.db_table, None, None)) |
|
365 select = Date((alias, column), lookup_type, |
|
366 self.connection.ops.date_trunc_sql) |
|
367 self.select = [select] |
|
368 self.select_fields = [None] |
|
369 self.select_related = False # See #7097. |
|
370 self.distinct = True |
|
371 self.order_by = order == 'ASC' and [1] or [-1] |
|
372 |
|
373 class CountQuery(Query): |
|
374 """ |
|
375 A CountQuery knows how to take a normal query which would select over |
|
376 multiple distinct columns and turn it into SQL that can be used on a |
|
377 variety of backends (it requires a select in the FROM clause). |
|
378 """ |
|
379 def get_from_clause(self): |
|
380 result, params = self._query.as_sql() |
|
381 return ['(%s) A1' % result], params |
|
382 |
|
383 def get_ordering(self): |
|
384 return () |
|
385 |