|
1 from itertools import izip |
|
2 from django.db.backends.util import truncate_name |
|
3 from django.db.models.sql import compiler |
|
4 from django.db.models.sql.constants import TABLE_NAME |
|
5 from django.db.models.sql.query import get_proxied_model |
|
6 |
|
7 SQLCompiler = compiler.SQLCompiler |
|
8 |
|
9 class GeoSQLCompiler(compiler.SQLCompiler): |
|
10 |
|
11 def get_columns(self, with_aliases=False): |
|
12 """ |
|
13 Return the list of columns to use in the select statement. If no |
|
14 columns have been specified, returns all columns relating to fields in |
|
15 the model. |
|
16 |
|
17 If 'with_aliases' is true, any column names that are duplicated |
|
18 (without the table names) are given unique aliases. This is needed in |
|
19 some cases to avoid ambiguitity with nested queries. |
|
20 |
|
21 This routine is overridden from Query to handle customized selection of |
|
22 geometry columns. |
|
23 """ |
|
24 qn = self.quote_name_unless_alias |
|
25 qn2 = self.connection.ops.quote_name |
|
26 result = ['(%s) AS %s' % (self.get_extra_select_format(alias) % col[0], qn2(alias)) |
|
27 for alias, col in self.query.extra_select.iteritems()] |
|
28 aliases = set(self.query.extra_select.keys()) |
|
29 if with_aliases: |
|
30 col_aliases = aliases.copy() |
|
31 else: |
|
32 col_aliases = set() |
|
33 if self.query.select: |
|
34 only_load = self.deferred_to_columns() |
|
35 # This loop customized for GeoQuery. |
|
36 for col, field in izip(self.query.select, self.query.select_fields): |
|
37 if isinstance(col, (list, tuple)): |
|
38 alias, column = col |
|
39 table = self.query.alias_map[alias][TABLE_NAME] |
|
40 if table in only_load and col not in only_load[table]: |
|
41 continue |
|
42 r = self.get_field_select(field, alias, column) |
|
43 if with_aliases: |
|
44 if col[1] in col_aliases: |
|
45 c_alias = 'Col%d' % len(col_aliases) |
|
46 result.append('%s AS %s' % (r, c_alias)) |
|
47 aliases.add(c_alias) |
|
48 col_aliases.add(c_alias) |
|
49 else: |
|
50 result.append('%s AS %s' % (r, qn2(col[1]))) |
|
51 aliases.add(r) |
|
52 col_aliases.add(col[1]) |
|
53 else: |
|
54 result.append(r) |
|
55 aliases.add(r) |
|
56 col_aliases.add(col[1]) |
|
57 else: |
|
58 result.append(col.as_sql(qn, self.connection)) |
|
59 |
|
60 if hasattr(col, 'alias'): |
|
61 aliases.add(col.alias) |
|
62 col_aliases.add(col.alias) |
|
63 |
|
64 elif self.query.default_cols: |
|
65 cols, new_aliases = self.get_default_columns(with_aliases, |
|
66 col_aliases) |
|
67 result.extend(cols) |
|
68 aliases.update(new_aliases) |
|
69 |
|
70 max_name_length = self.connection.ops.max_name_length() |
|
71 result.extend([ |
|
72 '%s%s' % ( |
|
73 self.get_extra_select_format(alias) % aggregate.as_sql(qn, self.connection), |
|
74 alias is not None |
|
75 and ' AS %s' % qn(truncate_name(alias, max_name_length)) |
|
76 or '' |
|
77 ) |
|
78 for alias, aggregate in self.query.aggregate_select.items() |
|
79 ]) |
|
80 |
|
81 # This loop customized for GeoQuery. |
|
82 for (table, col), field in izip(self.query.related_select_cols, self.query.related_select_fields): |
|
83 r = self.get_field_select(field, table, col) |
|
84 if with_aliases and col in col_aliases: |
|
85 c_alias = 'Col%d' % len(col_aliases) |
|
86 result.append('%s AS %s' % (r, c_alias)) |
|
87 aliases.add(c_alias) |
|
88 col_aliases.add(c_alias) |
|
89 else: |
|
90 result.append(r) |
|
91 aliases.add(r) |
|
92 col_aliases.add(col) |
|
93 |
|
94 self._select_aliases = aliases |
|
95 return result |
|
96 |
|
97 def get_default_columns(self, with_aliases=False, col_aliases=None, |
|
98 start_alias=None, opts=None, as_pairs=False): |
|
99 """ |
|
100 Computes the default columns for selecting every field in the base |
|
101 model. Will sometimes be called to pull in related models (e.g. via |
|
102 select_related), in which case "opts" and "start_alias" will be given |
|
103 to provide a starting point for the traversal. |
|
104 |
|
105 Returns a list of strings, quoted appropriately for use in SQL |
|
106 directly, as well as a set of aliases used in the select statement (if |
|
107 'as_pairs' is True, returns a list of (alias, col_name) pairs instead |
|
108 of strings as the first component and None as the second component). |
|
109 |
|
110 This routine is overridden from Query to handle customized selection of |
|
111 geometry columns. |
|
112 """ |
|
113 result = [] |
|
114 if opts is None: |
|
115 opts = self.query.model._meta |
|
116 aliases = set() |
|
117 only_load = self.deferred_to_columns() |
|
118 # Skip all proxy to the root proxied model |
|
119 proxied_model = get_proxied_model(opts) |
|
120 |
|
121 if start_alias: |
|
122 seen = {None: start_alias} |
|
123 for field, model in opts.get_fields_with_model(): |
|
124 if start_alias: |
|
125 try: |
|
126 alias = seen[model] |
|
127 except KeyError: |
|
128 if model is proxied_model: |
|
129 alias = start_alias |
|
130 else: |
|
131 link_field = opts.get_ancestor_link(model) |
|
132 alias = self.query.join((start_alias, model._meta.db_table, |
|
133 link_field.column, model._meta.pk.column)) |
|
134 seen[model] = alias |
|
135 else: |
|
136 # If we're starting from the base model of the queryset, the |
|
137 # aliases will have already been set up in pre_sql_setup(), so |
|
138 # we can save time here. |
|
139 alias = self.query.included_inherited_models[model] |
|
140 table = self.query.alias_map[alias][TABLE_NAME] |
|
141 if table in only_load and field.column not in only_load[table]: |
|
142 continue |
|
143 if as_pairs: |
|
144 result.append((alias, field.column)) |
|
145 aliases.add(alias) |
|
146 continue |
|
147 # This part of the function is customized for GeoQuery. We |
|
148 # see if there was any custom selection specified in the |
|
149 # dictionary, and set up the selection format appropriately. |
|
150 field_sel = self.get_field_select(field, alias) |
|
151 if with_aliases and field.column in col_aliases: |
|
152 c_alias = 'Col%d' % len(col_aliases) |
|
153 result.append('%s AS %s' % (field_sel, c_alias)) |
|
154 col_aliases.add(c_alias) |
|
155 aliases.add(c_alias) |
|
156 else: |
|
157 r = field_sel |
|
158 result.append(r) |
|
159 aliases.add(r) |
|
160 if with_aliases: |
|
161 col_aliases.add(field.column) |
|
162 return result, aliases |
|
163 |
|
164 def resolve_columns(self, row, fields=()): |
|
165 """ |
|
166 This routine is necessary so that distances and geometries returned |
|
167 from extra selection SQL get resolved appropriately into Python |
|
168 objects. |
|
169 """ |
|
170 values = [] |
|
171 aliases = self.query.extra_select.keys() |
|
172 if self.query.aggregates: |
|
173 # If we have an aggregate annotation, must extend the aliases |
|
174 # so their corresponding row values are included. |
|
175 aliases.extend([None for i in xrange(len(self.query.aggregates))]) |
|
176 |
|
177 # Have to set a starting row number offset that is used for |
|
178 # determining the correct starting row index -- needed for |
|
179 # doing pagination with Oracle. |
|
180 rn_offset = 0 |
|
181 if self.connection.ops.oracle: |
|
182 if self.query.high_mark is not None or self.query.low_mark: rn_offset = 1 |
|
183 index_start = rn_offset + len(aliases) |
|
184 |
|
185 # Converting any extra selection values (e.g., geometries and |
|
186 # distance objects added by GeoQuerySet methods). |
|
187 values = [self.query.convert_values(v, |
|
188 self.query.extra_select_fields.get(a, None), |
|
189 self.connection) |
|
190 for v, a in izip(row[rn_offset:index_start], aliases)] |
|
191 if self.connection.ops.oracle or getattr(self.query, 'geo_values', False): |
|
192 # We resolve the rest of the columns if we're on Oracle or if |
|
193 # the `geo_values` attribute is defined. |
|
194 for value, field in map(None, row[index_start:], fields): |
|
195 values.append(self.query.convert_values(value, field, connection=self.connection)) |
|
196 else: |
|
197 values.extend(row[index_start:]) |
|
198 return tuple(values) |
|
199 |
|
200 #### Routines unique to GeoQuery #### |
|
201 def get_extra_select_format(self, alias): |
|
202 sel_fmt = '%s' |
|
203 if alias in self.query.custom_select: |
|
204 sel_fmt = sel_fmt % self.query.custom_select[alias] |
|
205 return sel_fmt |
|
206 |
|
207 def get_field_select(self, field, alias=None, column=None): |
|
208 """ |
|
209 Returns the SELECT SQL string for the given field. Figures out |
|
210 if any custom selection SQL is needed for the column The `alias` |
|
211 keyword may be used to manually specify the database table where |
|
212 the column exists, if not in the model associated with this |
|
213 `GeoQuery`. Similarly, `column` may be used to specify the exact |
|
214 column name, rather than using the `column` attribute on `field`. |
|
215 """ |
|
216 sel_fmt = self.get_select_format(field) |
|
217 if field in self.query.custom_select: |
|
218 field_sel = sel_fmt % self.query.custom_select[field] |
|
219 else: |
|
220 field_sel = sel_fmt % self._field_column(field, alias, column) |
|
221 return field_sel |
|
222 |
|
223 def get_select_format(self, fld): |
|
224 """ |
|
225 Returns the selection format string, depending on the requirements |
|
226 of the spatial backend. For example, Oracle and MySQL require custom |
|
227 selection formats in order to retrieve geometries in OGC WKT. For all |
|
228 other fields a simple '%s' format string is returned. |
|
229 """ |
|
230 if self.connection.ops.select and hasattr(fld, 'geom_type'): |
|
231 # This allows operations to be done on fields in the SELECT, |
|
232 # overriding their values -- used by the Oracle and MySQL |
|
233 # spatial backends to get database values as WKT, and by the |
|
234 # `transform` method. |
|
235 sel_fmt = self.connection.ops.select |
|
236 |
|
237 # Because WKT doesn't contain spatial reference information, |
|
238 # the SRID is prefixed to the returned WKT to ensure that the |
|
239 # transformed geometries have an SRID different than that of the |
|
240 # field -- this is only used by `transform` for Oracle and |
|
241 # SpatiaLite backends. |
|
242 if self.query.transformed_srid and ( self.connection.ops.oracle or |
|
243 self.connection.ops.spatialite ): |
|
244 sel_fmt = "'SRID=%d;'||%s" % (self.query.transformed_srid, sel_fmt) |
|
245 else: |
|
246 sel_fmt = '%s' |
|
247 return sel_fmt |
|
248 |
|
249 # Private API utilities, subject to change. |
|
250 def _field_column(self, field, table_alias=None, column=None): |
|
251 """ |
|
252 Helper function that returns the database column for the given field. |
|
253 The table and column are returned (quoted) in the proper format, e.g., |
|
254 `"geoapp_city"."point"`. If `table_alias` is not specified, the |
|
255 database table associated with the model of this `GeoQuery` will be |
|
256 used. If `column` is specified, it will be used instead of the value |
|
257 in `field.column`. |
|
258 """ |
|
259 if table_alias is None: table_alias = self.query.model._meta.db_table |
|
260 return "%s.%s" % (self.quote_name_unless_alias(table_alias), |
|
261 self.connection.ops.quote_name(column or field.column)) |
|
262 |
|
263 class SQLInsertCompiler(compiler.SQLInsertCompiler, GeoSQLCompiler): |
|
264 pass |
|
265 |
|
266 class SQLDeleteCompiler(compiler.SQLDeleteCompiler, GeoSQLCompiler): |
|
267 pass |
|
268 |
|
269 class SQLUpdateCompiler(compiler.SQLUpdateCompiler, GeoSQLCompiler): |
|
270 pass |
|
271 |
|
272 class SQLAggregateCompiler(compiler.SQLAggregateCompiler, GeoSQLCompiler): |
|
273 pass |
|
274 |
|
275 class SQLDateCompiler(compiler.SQLDateCompiler, GeoSQLCompiler): |
|
276 pass |