django/db/models/sql/subqueries.py

   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 initialisation 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.select = []
 163         query.extra_select = {}
 164         query.add_fields([query.model._meta.pk.name])
 165
 166         # Now we adjust the current query: reset the where clause and get rid
 167         # of all the tables we don't need (since they're in the sub-select).
 168         self.where = self.where_class()
 169         if self.related_updates:
 170             idents = []
 171             for rows in query.execute_sql(MULTI):
 172                 idents.extend([r[0] for r in rows])
 173             self.add_filter(('pk__in', idents))
 174             self.related_ids = idents
 175         else:
 176             self.add_filter(('pk__in', query))
 177         for alias in self.tables[1:]:
 178             self.alias_refcount[alias] = 0
 179
 180     def clear_related(self, related_field, pk_list):
 181         """
 182         Set up and execute an update query that clears related entries for the
 183         keys in pk_list.
 184
 185         This is used by the QuerySet.delete_objects() method.
 186         """
 187         for offset in range(0, len(pk_list), GET_ITERATOR_CHUNK_SIZE):
 188             self.where = self.where_class()
 189             f = self.model._meta.pk
 190             self.where.add((None, f.column, f, 'in',
 191                     pk_list[offset : offset + GET_ITERATOR_CHUNK_SIZE]),
 192                     AND)
 193             self.values = [(related_field.column, None, '%s')]
 194             self.execute_sql(None)
 195
 196     def add_update_values(self, values):
 197         """
 198         Convert a dictionary of field name to value mappings into an update
 199         query. This is the entry point for the public update() method on
 200         querysets.
 201         """
 202         values_seq = []
 203         for name, val in values.iteritems():
 204             field, model, direct, m2m = self.model._meta.get_field_by_name(name)
 205             if not direct or m2m:
 206                 raise FieldError('Cannot update model field %r (only non-relations and foreign keys permitted).' % field)
 207             values_seq.append((field, model, val))
 208         return self.add_update_fields(values_seq)
 209
 210     def add_update_fields(self, values_seq):
 211         """
 212         Turn a sequence of (field, model, value) triples into an update query.
 213         Used by add_update_values() as well as the "fast" update path when
 214         saving models.
 215         """
 216         from django.db.models.base import Model
 217         for field, model, val in values_seq:
 218             # FIXME: Some sort of db_prep_* is probably more appropriate here.
 219             if field.rel and isinstance(val, Model):
 220                 val = val.pk
 221
 222             # Getting the placeholder for the field.
 223             if hasattr(field, 'get_placeholder'):
 224                 placeholder = field.get_placeholder(val)
 225             else:
 226                 placeholder = '%s'
 227
 228             if model:
 229                 self.add_related_update(model, field.column, val, placeholder)
 230             else:
 231                 self.values.append((field.column, val, placeholder))
 232
 233     def add_related_update(self, model, column, value, placeholder):
 234         """
 235         Adds (name, value) to an update query for an ancestor model.
 236
 237         Updates are coalesced so that we only run one update query per ancestor.
 238         """
 239         try:
 240             self.related_updates[model].append((column, value, placeholder))
 241         except KeyError:
 242             self.related_updates[model] = [(column, value, placeholder)]
 243
 244     def get_related_updates(self):
 245         """
 246         Returns a list of query objects: one for each update required to an
 247         ancestor model. Each query will have the same filtering conditions as
 248         the current query but will only update a single table.
 249         """
 250         if not self.related_updates:
 251             return []
 252         result = []
 253         for model, values in self.related_updates.iteritems():
 254             query = UpdateQuery(model, self.connection)
 255             query.values = values
 256             if self.related_ids:
 257                 query.add_filter(('pk__in', self.related_ids))
 258             result.append(query)
 259         return result
 260
 261 class InsertQuery(Query):
 262     def __init__(self, *args, **kwargs):
 263         super(InsertQuery, self).__init__(*args, **kwargs)
 264         self.columns = []
 265         self.values = []
 266         self.params = ()
 267
 268     def clone(self, klass=None, **kwargs):
 269         extras = {'columns': self.columns[:], 'values': self.values[:],
 270                 'params': self.params}
 271         return super(InsertQuery, self).clone(klass, extras)
 272
 273     def as_sql(self):
 274         # We don't need quote_name_unless_alias() here, since these are all
 275         # going to be column names (so we can avoid the extra overhead).
 276         qn = self.connection.ops.quote_name
 277         result = ['INSERT INTO %s' % qn(self.model._meta.db_table)]
 278         result.append('(%s)' % ', '.join([qn(c) for c in self.columns]))
 279         result.append('VALUES (%s)' % ', '.join(self.values))
 280         return ' '.join(result), self.params
 281
 282     def execute_sql(self, return_id=False):
 283         cursor = super(InsertQuery, self).execute_sql(None)
 284         if return_id:
 285             return self.connection.ops.last_insert_id(cursor,
 286                     self.model._meta.db_table, self.model._meta.pk.column)
 287
 288     def insert_values(self, insert_values, raw_values=False):
 289         """
 290         Set up the insert query from the 'insert_values' dictionary. The
 291         dictionary gives the model field names and their target values.
 292
 293         If 'raw_values' is True, the values in the 'insert_values' dictionary
 294         are inserted directly into the query, rather than passed as SQL
 295         parameters. This provides a way to insert NULL and DEFAULT keywords
 296         into the query, for example.
 297         """
 298         placeholders, values = [], []
 299         for field, val in insert_values:
 300             if hasattr(field, 'get_placeholder'):
 301                 # Some fields (e.g. geo fields) need special munging before
 302                 # they can be inserted.
 303                 placeholders.append(field.get_placeholder(val))
 304             else:
 305                 placeholders.append('%s')
 306
 307             self.columns.append(field.column)
 308             values.append(val)
 309         if raw_values:
 310             self.values.extend(values)
 311         else:
 312             self.params += tuple(values)
 313             self.values.extend(placeholders)
 314
 315 class DateQuery(Query):
 316     """
 317     A DateQuery is a normal query, except that it specifically selects a single
 318     date field. This requires some special handling when converting the results
 319     back to Python objects, so we put it in a separate class.
 320     """
 321     def results_iter(self):
 322         """
 323         Returns an iterator over the results from executing this query.
 324         """
 325         resolve_columns = hasattr(self, 'resolve_columns')
 326         if resolve_columns:
 327             from django.db.models.fields import DateTimeField
 328             fields = [DateTimeField()]
 329         else:
 330             from django.db.backends.util import typecast_timestamp
 331             needs_string_cast = self.connection.features.needs_datetime_string_cast
 332
 333         offset = len(self.extra_select)
 334         for rows in self.execute_sql(MULTI):
 335             for row in rows:
 336                 date = row[offset]
 337                 if resolve_columns:
 338                     date = self.resolve_columns([date], fields)[0]
 339                 elif needs_string_cast:
 340                     date = typecast_timestamp(str(date))
 341                 yield date
 342
 343     def add_date_select(self, column, lookup_type, order='ASC'):
 344         """
 345         Converts the query into a date extraction query.
 346         """
 347         alias = self.join((None, self.model._meta.db_table, None, None))
 348         select = Date((alias, column), lookup_type,
 349                 self.connection.ops.date_trunc_sql)
 350         self.select = [select]
 351         self.select_fields = [None]
 352         self.distinct = True
 353         self.order_by = order == 'ASC' and [1] or [-1]
 354
 355 class CountQuery(Query):
 356     """
 357     A CountQuery knows how to take a normal query which would select over
 358     multiple distinct columns and turn it into SQL that can be used on a
 359     variety of backends (it requires a select in the FROM clause).
 360     """
 361     def get_from_clause(self):
 362         result, params = self._query.as_sql()
 363         return ['(%s) A1' % result], params
 364
 365     def get_ordering(self):
 366         return ()
 367