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