1
from django.core.exceptions import FieldError
2
from django.db import connections
3
from django.db.backends.util import truncate_name
4
from django.db.models.sql.constants import *
5
from django.db.models.sql.datastructures import EmptyResultSet
6
from django.db.models.sql.expressions import SQLEvaluator
7
from django.db.models.sql.query import get_proxied_model, get_order_dir, \
8
select_related_descend, Query
10
class SQLCompiler(object):
11
def __init__(self, query, connection, using):
13
self.connection = connection
17
def pre_sql_setup(self):
19
Does any necessary class setup immediately prior to producing SQL. This
20
is for things that can't necessarily be done in __init__ because we
21
might not have all the pieces in place at that time.
23
if not self.query.tables:
24
self.query.join((None, self.query.model._meta.db_table, None, None))
25
if (not self.query.select and self.query.default_cols and not
26
self.query.included_inherited_models):
27
self.query.setup_inherited_models()
28
if self.query.select_related and not self.query.related_select_cols:
29
self.fill_related_selections()
31
def quote_name_unless_alias(self, name):
33
A wrapper around connection.ops.quote_name that doesn't quote aliases
34
for table names. This avoids problems with some SQL dialects that treat
35
quoted strings specially (e.g. PostgreSQL).
37
if name in self.quote_cache:
38
return self.quote_cache[name]
39
if ((name in self.query.alias_map and name not in self.query.table_map) or
40
name in self.query.extra_select):
41
self.quote_cache[name] = name
43
r = self.connection.ops.quote_name(name)
44
self.quote_cache[name] = r
47
def as_sql(self, with_limits=True, with_col_aliases=False):
49
Creates the SQL for this query. Returns the SQL string and list of
52
If 'with_limits' is False, any limit/offset information is not included
56
out_cols = self.get_columns(with_col_aliases)
57
ordering, ordering_group_by = self.get_ordering()
59
# This must come after 'select' and 'ordering' -- see docstring of
60
# get_from_clause() for details.
61
from_, f_params = self.get_from_clause()
63
qn = self.quote_name_unless_alias
65
where, w_params = self.query.where.as_sql(qn=qn, connection=self.connection)
66
having, h_params = self.query.having.as_sql(qn=qn, connection=self.connection)
68
for val in self.query.extra_select.itervalues():
72
if self.query.distinct:
73
result.append('DISTINCT')
74
result.append(', '.join(out_cols + self.query.ordering_aliases))
78
params.extend(f_params)
81
result.append('WHERE %s' % where)
82
params.extend(w_params)
84
grouping, gb_params = self.get_grouping()
87
# If the backend can't group by PK (i.e., any database
88
# other than MySQL), then any fields mentioned in the
89
# ordering clause needs to be in the group by clause.
90
if not self.connection.features.allows_group_by_pk:
91
for col, col_params in ordering_group_by:
92
if col not in grouping:
93
grouping.append(str(col))
94
gb_params.extend(col_params)
96
ordering = self.connection.ops.force_no_ordering()
97
result.append('GROUP BY %s' % ', '.join(grouping))
98
params.extend(gb_params)
101
result.append('HAVING %s' % having)
102
params.extend(h_params)
105
result.append('ORDER BY %s' % ', '.join(ordering))
108
if self.query.high_mark is not None:
109
result.append('LIMIT %d' % (self.query.high_mark - self.query.low_mark))
110
if self.query.low_mark:
111
if self.query.high_mark is None:
112
val = self.connection.ops.no_limit_value()
114
result.append('LIMIT %d' % val)
115
result.append('OFFSET %d' % self.query.low_mark)
117
return ' '.join(result), tuple(params)
119
def as_nested_sql(self):
121
Perform the same functionality as the as_sql() method, returning an
122
SQL string and parameters. However, the alias prefixes are bumped
123
beforehand (in a copy -- the current query isn't changed), and any
124
ordering is removed if the query is unsliced.
126
Used when nesting this query inside another.
128
obj = self.query.clone()
129
if obj.low_mark == 0 and obj.high_mark is None:
130
# If there is no slicing in use, then we can safely drop all ordering
131
obj.clear_ordering(True)
133
return obj.get_compiler(connection=self.connection).as_sql()
135
def get_columns(self, with_aliases=False):
137
Returns the list of columns to use in the select statement. If no
138
columns have been specified, returns all columns relating to fields in
141
If 'with_aliases' is true, any column names that are duplicated
142
(without the table names) are given unique aliases. This is needed in
143
some cases to avoid ambiguity with nested queries.
145
qn = self.quote_name_unless_alias
146
qn2 = self.connection.ops.quote_name
147
result = ['(%s) AS %s' % (col[0], qn2(alias)) for alias, col in self.query.extra_select.iteritems()]
148
aliases = set(self.query.extra_select.keys())
150
col_aliases = aliases.copy()
153
if self.query.select:
154
only_load = self.deferred_to_columns()
155
for col in self.query.select:
156
if isinstance(col, (list, tuple)):
158
table = self.query.alias_map[alias][TABLE_NAME]
159
if table in only_load and col not in only_load[table]:
161
r = '%s.%s' % (qn(alias), qn(column))
163
if col[1] in col_aliases:
164
c_alias = 'Col%d' % len(col_aliases)
165
result.append('%s AS %s' % (r, c_alias))
167
col_aliases.add(c_alias)
169
result.append('%s AS %s' % (r, qn2(col[1])))
171
col_aliases.add(col[1])
175
col_aliases.add(col[1])
177
result.append(col.as_sql(qn, self.connection))
179
if hasattr(col, 'alias'):
180
aliases.add(col.alias)
181
col_aliases.add(col.alias)
183
elif self.query.default_cols:
184
cols, new_aliases = self.get_default_columns(with_aliases,
187
aliases.update(new_aliases)
189
max_name_length = self.connection.ops.max_name_length()
192
aggregate.as_sql(qn, self.connection),
194
and ' AS %s' % qn(truncate_name(alias, max_name_length))
197
for alias, aggregate in self.query.aggregate_select.items()
200
for table, col in self.query.related_select_cols:
201
r = '%s.%s' % (qn(table), qn(col))
202
if with_aliases and col in col_aliases:
203
c_alias = 'Col%d' % len(col_aliases)
204
result.append('%s AS %s' % (r, c_alias))
206
col_aliases.add(c_alias)
212
self._select_aliases = aliases
215
def get_default_columns(self, with_aliases=False, col_aliases=None,
216
start_alias=None, opts=None, as_pairs=False, local_only=False):
218
Computes the default columns for selecting every field in the base
219
model. Will sometimes be called to pull in related models (e.g. via
220
select_related), in which case "opts" and "start_alias" will be given
221
to provide a starting point for the traversal.
223
Returns a list of strings, quoted appropriately for use in SQL
224
directly, as well as a set of aliases used in the select statement (if
225
'as_pairs' is True, returns a list of (alias, col_name) pairs instead
226
of strings as the first component and None as the second component).
230
opts = self.query.model._meta
231
qn = self.quote_name_unless_alias
232
qn2 = self.connection.ops.quote_name
234
only_load = self.deferred_to_columns()
235
# Skip all proxy to the root proxied model
236
proxied_model = get_proxied_model(opts)
239
seen = {None: start_alias}
240
for field, model in opts.get_fields_with_model():
241
if local_only and model is not None:
247
if model is proxied_model:
250
link_field = opts.get_ancestor_link(model)
251
alias = self.query.join((start_alias, model._meta.db_table,
252
link_field.column, model._meta.pk.column))
255
# If we're starting from the base model of the queryset, the
256
# aliases will have already been set up in pre_sql_setup(), so
257
# we can save time here.
258
alias = self.query.included_inherited_models[model]
259
table = self.query.alias_map[alias][TABLE_NAME]
260
if table in only_load and field.column not in only_load[table]:
263
result.append((alias, field.column))
266
if with_aliases and field.column in col_aliases:
267
c_alias = 'Col%d' % len(col_aliases)
268
result.append('%s.%s AS %s' % (qn(alias),
269
qn2(field.column), c_alias))
270
col_aliases.add(c_alias)
273
r = '%s.%s' % (qn(alias), qn2(field.column))
277
col_aliases.add(field.column)
278
return result, aliases
280
def get_ordering(self):
282
Returns a tuple containing a list representing the SQL elements in the
283
"order by" clause, and the list of SQL elements that need to be added
284
to the GROUP BY clause as a result of the ordering.
286
Also sets the ordering_aliases attribute on this instance to a list of
287
extra aliases needed in the select.
289
Determining the ordering SQL can change the tables we need to include,
290
so this should be run *before* get_from_clause().
292
if self.query.extra_order_by:
293
ordering = self.query.extra_order_by
294
elif not self.query.default_ordering:
295
ordering = self.query.order_by
297
ordering = self.query.order_by or self.query.model._meta.ordering
298
qn = self.quote_name_unless_alias
299
qn2 = self.connection.ops.quote_name
300
distinct = self.query.distinct
301
select_aliases = self._select_aliases
304
ordering_aliases = []
305
if self.query.standard_ordering:
306
asc, desc = ORDER_DIR['ASC']
308
asc, desc = ORDER_DIR['DESC']
310
# It's possible, due to model inheritance, that normal usage might try
311
# to include the same field more than once in the ordering. We track
312
# the table/column pairs we use and discard any after the first use.
313
processed_pairs = set()
315
for field in ordering:
317
result.append(self.connection.ops.random_function_sql())
319
if isinstance(field, int):
325
result.append('%s %s' % (field, order))
326
group_by.append((field, []))
328
col, order = get_order_dir(field, asc)
329
if col in self.query.aggregate_select:
330
result.append('%s %s' % (col, order))
333
# This came in through an extra(order_by=...) addition. Pass it
335
table, col = col.split('.', 1)
336
if (table, col) not in processed_pairs:
337
elt = '%s.%s' % (qn(table), col)
338
processed_pairs.add((table, col))
339
if not distinct or elt in select_aliases:
340
result.append('%s %s' % (elt, order))
341
group_by.append((elt, []))
342
elif get_order_dir(field)[0] not in self.query.extra_select:
343
# 'col' is of the form 'field' or 'field1__field2' or
344
# '-field1__field2__field', etc.
345
for table, col, order in self.find_ordering_name(field,
346
self.query.model._meta, default_order=asc):
347
if (table, col) not in processed_pairs:
348
elt = '%s.%s' % (qn(table), qn2(col))
349
processed_pairs.add((table, col))
350
if distinct and elt not in select_aliases:
351
ordering_aliases.append(elt)
352
result.append('%s %s' % (elt, order))
353
group_by.append((elt, []))
356
if distinct and col not in select_aliases:
357
ordering_aliases.append(elt)
358
result.append('%s %s' % (elt, order))
359
group_by.append(self.query.extra_select[col])
360
self.query.ordering_aliases = ordering_aliases
361
return result, group_by
363
def find_ordering_name(self, name, opts, alias=None, default_order='ASC',
366
Returns the table alias (the name might be ambiguous, the alias will
367
not be) and column name for ordering by the given 'name' parameter.
368
The 'name' is of the form 'field1__field2__...__fieldN'.
370
name, order = get_order_dir(name, default_order)
371
pieces = name.split(LOOKUP_SEP)
373
alias = self.query.get_initial_alias()
374
field, target, opts, joins, last, extra = self.query.setup_joins(pieces,
379
# To avoid inadvertent trimming of a necessary alias, use the
380
# refcount to show that we are referencing a non-relation field on
382
self.query.ref_alias(alias)
384
# Must use left outer joins for nullable fields and their relations.
385
self.query.promote_alias_chain(joins,
386
self.query.alias_map[joins[0]][JOIN_TYPE] == self.query.LOUTER)
388
# If we get to this point and the field is a relation to another model,
389
# append the default ordering for that model.
390
if field.rel and len(joins) > 1 and opts.ordering:
391
# Firstly, avoid infinite loops.
394
join_tuple = tuple([self.query.alias_map[j][TABLE_NAME] for j in joins])
395
if join_tuple in already_seen:
396
raise FieldError('Infinite loop caused by ordering.')
397
already_seen.add(join_tuple)
400
for item in opts.ordering:
401
results.extend(self.find_ordering_name(item, opts, alias,
402
order, already_seen))
406
# We have to do the same "final join" optimisation as in
407
# add_filter, since the final column might not otherwise be part of
408
# the select set (so we can't order on it).
410
join = self.query.alias_map[alias]
411
if col != join[RHS_JOIN_COL]:
413
self.query.unref_alias(alias)
414
alias = join[LHS_ALIAS]
415
col = join[LHS_JOIN_COL]
416
return [(alias, col, order)]
418
def get_from_clause(self):
420
Returns a list of strings that are joined together to go after the
421
"FROM" part of the query, as well as a list any extra parameters that
422
need to be included. Sub-classes, can override this to create a
423
from-clause via a "select".
425
This should only be called after any SQL construction methods that
426
might change the tables we need. This means the select columns and
427
ordering must be done first.
430
qn = self.quote_name_unless_alias
431
qn2 = self.connection.ops.quote_name
433
for alias in self.query.tables:
434
if not self.query.alias_refcount[alias]:
437
name, alias, join_type, lhs, lhs_col, col, nullable = self.query.alias_map[alias]
439
# Extra tables can end up in self.tables, but not in the
440
# alias_map if they aren't in a join. That's OK. We skip them.
442
alias_str = (alias != name and ' %s' % alias or '')
443
if join_type and not first:
444
result.append('%s %s%s ON (%s.%s = %s.%s)'
445
% (join_type, qn(name), alias_str, qn(lhs),
446
qn2(lhs_col), qn(alias), qn2(col)))
448
connector = not first and ', ' or ''
449
result.append('%s%s%s' % (connector, qn(name), alias_str))
451
for t in self.query.extra_tables:
452
alias, unused = self.query.table_alias(t)
453
# Only add the alias if it's not already present (the table_alias()
454
# calls increments the refcount, so an alias refcount of one means
455
# this is the only reference.
456
if alias not in self.query.alias_map or self.query.alias_refcount[alias] == 1:
457
connector = not first and ', ' or ''
458
result.append('%s%s' % (connector, qn(alias)))
462
def get_grouping(self):
464
Returns a tuple representing the SQL elements in the "group by" clause.
466
qn = self.quote_name_unless_alias
467
result, params = [], []
468
if self.query.group_by is not None:
469
if len(self.query.model._meta.fields) == len(self.query.select) and \
470
self.connection.features.allows_group_by_pk:
471
self.query.group_by = [(self.query.model._meta.db_table, self.query.model._meta.pk.column)]
473
group_by = self.query.group_by or []
476
for extra_select, extra_params in self.query.extra_select.itervalues():
477
extra_selects.append(extra_select)
478
params.extend(extra_params)
479
for col in group_by + self.query.related_select_cols + extra_selects:
480
if isinstance(col, (list, tuple)):
481
result.append('%s.%s' % (qn(col[0]), qn(col[1])))
482
elif hasattr(col, 'as_sql'):
483
result.append(col.as_sql(qn))
485
result.append('(%s)' % str(col))
486
return result, params
488
def fill_related_selections(self, opts=None, root_alias=None, cur_depth=1,
489
used=None, requested=None, restricted=None, nullable=None,
490
dupe_set=None, avoid_set=None):
492
Fill in the information needed for a select_related query. The current
493
depth is measured as the number of connections away from the root model
494
(for example, cur_depth=1 means we are looking at models with direct
495
connections to the root model).
497
if not restricted and self.query.max_depth and cur_depth > self.query.max_depth:
498
# We've recursed far enough; bail out.
502
opts = self.query.get_meta()
503
root_alias = self.query.get_initial_alias()
504
self.query.related_select_cols = []
505
self.query.related_select_fields = []
510
if avoid_set is None:
512
orig_dupe_set = dupe_set
514
# Setup for the case when only particular related fields should be
515
# included in the related selection.
516
if requested is None:
517
if isinstance(self.query.select_related, dict):
518
requested = self.query.select_related
523
for f, model in opts.get_fields_with_model():
524
if not select_related_descend(f, restricted, requested):
526
# The "avoid" set is aliases we want to avoid just for this
527
# particular branch of the recursion. They aren't permanently
528
# forbidden from reuse in the related selection tables (which is
529
# what "used" specifies).
530
avoid = avoid_set.copy()
531
dupe_set = orig_dupe_set.copy()
532
table = f.rel.to._meta.db_table
533
promote = nullable or f.null
538
for int_model in opts.get_base_chain(model):
539
# Proxy model have elements in base chain
540
# with no parents, assign the new options
541
# object and skip to the next base in that
543
if not int_opts.parents[int_model]:
544
int_opts = int_model._meta
546
lhs_col = int_opts.parents[int_model].column
547
dedupe = lhs_col in opts.duplicate_targets
549
avoid.update(self.query.dupe_avoidance.get((id(opts), lhs_col),
551
dupe_set.add((opts, lhs_col))
552
int_opts = int_model._meta
553
alias = self.query.join((alias, int_opts.db_table, lhs_col,
554
int_opts.pk.column), exclusions=used,
556
alias_chain.append(alias)
557
for (dupe_opts, dupe_col) in dupe_set:
558
self.query.update_dupe_avoidance(dupe_opts, dupe_col, alias)
559
if self.query.alias_map[root_alias][JOIN_TYPE] == self.query.LOUTER:
560
self.query.promote_alias_chain(alias_chain, True)
564
dedupe = f.column in opts.duplicate_targets
565
if dupe_set or dedupe:
566
avoid.update(self.query.dupe_avoidance.get((id(opts), f.column), ()))
568
dupe_set.add((opts, f.column))
570
alias = self.query.join((alias, table, f.column,
571
f.rel.get_related_field().column),
572
exclusions=used.union(avoid), promote=promote)
574
columns, aliases = self.get_default_columns(start_alias=alias,
575
opts=f.rel.to._meta, as_pairs=True)
576
self.query.related_select_cols.extend(columns)
577
if self.query.alias_map[alias][JOIN_TYPE] == self.query.LOUTER:
578
self.query.promote_alias_chain(aliases, True)
579
self.query.related_select_fields.extend(f.rel.to._meta.fields)
581
next = requested.get(f.name, {})
584
new_nullable = f.null or promote
585
for dupe_opts, dupe_col in dupe_set:
586
self.query.update_dupe_avoidance(dupe_opts, dupe_col, alias)
587
self.fill_related_selections(f.rel.to._meta, alias, cur_depth + 1,
588
used, next, restricted, new_nullable, dupe_set, avoid)
593
for o in opts.get_all_related_objects()
596
for f, model in related_fields:
597
if not select_related_descend(f, restricted, requested, reverse=True):
599
# The "avoid" set is aliases we want to avoid just for this
600
# particular branch of the recursion. They aren't permanently
601
# forbidden from reuse in the related selection tables (which is
602
# what "used" specifies).
603
avoid = avoid_set.copy()
604
dupe_set = orig_dupe_set.copy()
605
table = model._meta.db_table
610
chain = opts.get_base_chain(f.rel.to)
611
if chain is not None:
612
for int_model in chain:
613
# Proxy model have elements in base chain
614
# with no parents, assign the new options
615
# object and skip to the next base in that
617
if not int_opts.parents[int_model]:
618
int_opts = int_model._meta
620
lhs_col = int_opts.parents[int_model].column
621
dedupe = lhs_col in opts.duplicate_targets
623
avoid.update((self.query.dupe_avoidance.get(id(opts), lhs_col),
625
dupe_set.add((opts, lhs_col))
626
int_opts = int_model._meta
627
alias = self.query.join(
628
(alias, int_opts.db_table, lhs_col, int_opts.pk.column),
629
exclusions=used, promote=True, reuse=used
631
alias_chain.append(alias)
632
for dupe_opts, dupe_col in dupe_set:
633
self.query.update_dupe_avoidance(dupe_opts, dupe_col, alias)
634
dedupe = f.column in opts.duplicate_targets
635
if dupe_set or dedupe:
636
avoid.update(self.query.dupe_avoidance.get((id(opts), f.column), ()))
638
dupe_set.add((opts, f.column))
639
alias = self.query.join(
640
(alias, table, f.rel.get_related_field().column, f.column),
641
exclusions=used.union(avoid),
645
columns, aliases = self.get_default_columns(start_alias=alias,
646
opts=model._meta, as_pairs=True, local_only=True)
647
self.query.related_select_cols.extend(columns)
648
self.query.related_select_fields.extend(model._meta.fields)
650
next = requested.get(f.related_query_name(), {})
651
new_nullable = f.null or None
653
self.fill_related_selections(model._meta, table, cur_depth+1,
654
used, next, restricted, new_nullable)
656
def deferred_to_columns(self):
658
Converts the self.deferred_loading data structure to mapping of table
659
names to sets of column names which are to be loaded. Returns the
663
self.query.deferred_to_data(columns, self.query.deferred_to_columns_cb)
666
def results_iter(self):
668
Returns an iterator over the results from executing this query.
670
resolve_columns = hasattr(self, 'resolve_columns')
672
for rows in self.execute_sql(MULTI):
676
# We only set this up here because
677
# related_select_fields isn't populated until
678
# execute_sql() has been called.
679
if self.query.select_fields:
680
fields = self.query.select_fields + self.query.related_select_fields
682
fields = self.query.model._meta.fields
683
# If the field was deferred, exclude it from being passed
684
# into `resolve_columns` because it wasn't selected.
685
only_load = self.deferred_to_columns()
687
db_table = self.query.model._meta.db_table
688
fields = [f for f in fields if db_table in only_load and
689
f.column in only_load[db_table]]
690
row = self.resolve_columns(row, fields)
692
if self.query.aggregate_select:
693
aggregate_start = len(self.query.extra_select.keys()) + len(self.query.select)
694
aggregate_end = aggregate_start + len(self.query.aggregate_select)
695
row = tuple(row[:aggregate_start]) + tuple([
696
self.query.resolve_aggregate(value, aggregate, self.connection)
697
for (alias, aggregate), value
698
in zip(self.query.aggregate_select.items(), row[aggregate_start:aggregate_end])
699
]) + tuple(row[aggregate_end:])
703
def execute_sql(self, result_type=MULTI):
705
Run the query against the database and returns the result(s). The
706
return value is a single data item if result_type is SINGLE, or an
707
iterator over the results if the result_type is MULTI.
709
result_type is either MULTI (use fetchmany() to retrieve all rows),
710
SINGLE (only retrieve a single row), or None. In this last case, the
711
cursor is returned if any query is executed, since it's used by
712
subclasses such as InsertQuery). It's possible, however, that no query
713
is needed, as the filters describe an empty set. In that case, None is
714
returned, to avoid any unnecessary database interaction.
717
sql, params = self.as_sql()
720
except EmptyResultSet:
721
if result_type == MULTI:
726
cursor = self.connection.cursor()
727
cursor.execute(sql, params)
731
if result_type == SINGLE:
732
if self.query.ordering_aliases:
733
return cursor.fetchone()[:-len(self.query.ordering_aliases)]
734
return cursor.fetchone()
737
if self.query.ordering_aliases:
738
result = order_modified_iter(cursor, len(self.query.ordering_aliases),
739
self.connection.features.empty_fetchmany_value)
741
result = iter((lambda: cursor.fetchmany(GET_ITERATOR_CHUNK_SIZE)),
742
self.connection.features.empty_fetchmany_value)
743
if not self.connection.features.can_use_chunked_reads:
744
# If we are using non-chunked reads, we return the same data
745
# structure as normally, but ensure it is all read into memory
746
# before going any further.
751
class SQLInsertCompiler(SQLCompiler):
752
def placeholder(self, field, val):
754
# A field value of None means the value is raw.
756
elif hasattr(field, 'get_placeholder'):
757
# Some fields (e.g. geo fields) need special munging before
758
# they can be inserted.
759
return field.get_placeholder(val, self.connection)
761
# Return the common case for the placeholder
765
# We don't need quote_name_unless_alias() here, since these are all
766
# going to be column names (so we can avoid the extra overhead).
767
qn = self.connection.ops.quote_name
768
opts = self.query.model._meta
769
result = ['INSERT INTO %s' % qn(opts.db_table)]
770
result.append('(%s)' % ', '.join([qn(c) for c in self.query.columns]))
771
values = [self.placeholder(*v) for v in self.query.values]
772
result.append('VALUES (%s)' % ', '.join(values))
773
params = self.query.params
774
if self.return_id and self.connection.features.can_return_id_from_insert:
775
col = "%s.%s" % (qn(opts.db_table), qn(opts.pk.column))
776
r_fmt, r_params = self.connection.ops.return_insert_id()
777
result.append(r_fmt % col)
778
params = params + r_params
779
return ' '.join(result), params
781
def execute_sql(self, return_id=False):
782
self.return_id = return_id
783
cursor = super(SQLInsertCompiler, self).execute_sql(None)
784
if not (return_id and cursor):
786
if self.connection.features.can_return_id_from_insert:
787
return self.connection.ops.fetch_returned_insert_id(cursor)
788
return self.connection.ops.last_insert_id(cursor,
789
self.query.model._meta.db_table, self.query.model._meta.pk.column)
792
class SQLDeleteCompiler(SQLCompiler):
795
Creates the SQL for this query. Returns the SQL string and list of
798
assert len(self.query.tables) == 1, \
799
"Can only delete from one table at a time."
800
qn = self.quote_name_unless_alias
801
result = ['DELETE FROM %s' % qn(self.query.tables[0])]
802
where, params = self.query.where.as_sql(qn=qn, connection=self.connection)
803
result.append('WHERE %s' % where)
804
return ' '.join(result), tuple(params)
806
class SQLUpdateCompiler(SQLCompiler):
809
Creates the SQL for this query. Returns the SQL string and list of
812
from django.db.models.base import Model
815
if not self.query.values:
817
table = self.query.tables[0]
818
qn = self.quote_name_unless_alias
819
result = ['UPDATE %s' % qn(table)]
821
values, update_params = [], []
822
for field, model, val in self.query.values:
823
if hasattr(val, 'prepare_database_save'):
824
val = val.prepare_database_save(field)
826
val = field.get_db_prep_save(val, connection=self.connection)
828
# Getting the placeholder for the field.
829
if hasattr(field, 'get_placeholder'):
830
placeholder = field.get_placeholder(val, self.connection)
834
if hasattr(val, 'evaluate'):
835
val = SQLEvaluator(val, self.query, allow_joins=False)
837
if hasattr(val, 'as_sql'):
838
sql, params = val.as_sql(qn, self.connection)
839
values.append('%s = %s' % (qn(name), sql))
840
update_params.extend(params)
841
elif val is not None:
842
values.append('%s = %s' % (qn(name), placeholder))
843
update_params.append(val)
845
values.append('%s = NULL' % qn(name))
848
result.append(', '.join(values))
849
where, params = self.query.where.as_sql(qn=qn, connection=self.connection)
851
result.append('WHERE %s' % where)
852
return ' '.join(result), tuple(update_params + params)
854
def execute_sql(self, result_type):
856
Execute the specified update. Returns the number of rows affected by
857
the primary update query. The "primary update query" is the first
858
non-empty query that is executed. Row counts for any subsequent,
859
related queries are not available.
861
cursor = super(SQLUpdateCompiler, self).execute_sql(result_type)
862
rows = cursor and cursor.rowcount or 0
863
is_empty = cursor is None
865
for query in self.query.get_related_updates():
866
aux_rows = query.get_compiler(self.using).execute_sql(result_type)
872
def pre_sql_setup(self):
874
If the update depends on results from other tables, we need to do some
875
munging of the "where" conditions to match the format required for
876
(portable) SQL updates. That is done here.
878
Further, if we are going to be running multiple updates, we pull out
879
the id values to update at this point so that they don't change as a
880
result of the progressive updates.
882
self.query.select_related = False
883
self.query.clear_ordering(True)
884
super(SQLUpdateCompiler, self).pre_sql_setup()
885
count = self.query.count_active_tables()
886
if not self.query.related_updates and count == 1:
889
# We need to use a sub-select in the where clause to filter on things
891
query = self.query.clone(klass=Query)
895
query.add_fields([query.model._meta.pk.name])
896
must_pre_select = count > 1 and not self.connection.features.update_can_self_select
898
# Now we adjust the current query: reset the where clause and get rid
899
# of all the tables we don't need (since they're in the sub-select).
900
self.query.where = self.query.where_class()
901
if self.query.related_updates or must_pre_select:
902
# Either we're using the idents in multiple update queries (so
903
# don't want them to change), or the db backend doesn't support
904
# selecting from the updating table (e.g. MySQL).
906
for rows in query.get_compiler(self.using).execute_sql(MULTI):
907
idents.extend([r[0] for r in rows])
908
self.query.add_filter(('pk__in', idents))
909
self.query.related_ids = idents
911
# The fast path. Filters and updates in one query.
912
self.query.add_filter(('pk__in', query))
913
for alias in self.query.tables[1:]:
914
self.query.alias_refcount[alias] = 0
916
class SQLAggregateCompiler(SQLCompiler):
917
def as_sql(self, qn=None):
919
Creates the SQL for this query. Returns the SQL string and list of
923
qn = self.quote_name_unless_alias
924
sql = ('SELECT %s FROM (%s) subquery' % (
926
aggregate.as_sql(qn, self.connection)
927
for aggregate in self.query.aggregate_select.values()
931
params = self.query.sub_params
934
class SQLDateCompiler(SQLCompiler):
935
def results_iter(self):
937
Returns an iterator over the results from executing this query.
939
resolve_columns = hasattr(self, 'resolve_columns')
941
from django.db.models.fields import DateTimeField
942
fields = [DateTimeField()]
944
from django.db.backends.util import typecast_timestamp
945
needs_string_cast = self.connection.features.needs_datetime_string_cast
947
offset = len(self.query.extra_select)
948
for rows in self.execute_sql(MULTI):
952
date = self.resolve_columns(row, fields)[offset]
953
elif needs_string_cast:
954
date = typecast_timestamp(str(date))
960
Returns an iterator containing no results.
962
yield iter([]).next()
965
def order_modified_iter(cursor, trim, sentinel):
967
Yields blocks of rows from a cursor. We use this iterator in the special
968
case when extra output columns have been added to support ordering
969
requirements. We must trim those extra columns before anything else can use
970
the results, since they're only needed to make the SQL valid.
972
for rows in iter((lambda: cursor.fetchmany(GET_ITERATOR_CHUNK_SIZE)),
974
yield [r[:-trim] for r in rows]