svn-gvsig-desktop / trunk / org.gvsig.desktop / org.gvsig.desktop.compat.cdc / org.gvsig.fmap.dal / org.gvsig.fmap.dal.db / org.gvsig.fmap.dal.db.jdbc / src / main / java / org / gvsig / fmap / dal / store / jdbc2 / spi / operations / ResultSetForSetProviderOperation.java @ 46401
History | View | Annotate | Download (30.1 KB)
1 | 45065 | jjdelcerro | /**
|
---|---|---|---|
2 | * gvSIG. Desktop Geographic Information System.
|
||
3 | *
|
||
4 | * Copyright (C) 2007-2020 gvSIG Association.
|
||
5 | *
|
||
6 | * This program is free software; you can redistribute it and/or
|
||
7 | * modify it under the terms of the GNU General Public License
|
||
8 | * as published by the Free Software Foundation; either version 3
|
||
9 | * of the License, or (at your option) any later version.
|
||
10 | *
|
||
11 | * This program is distributed in the hope that it will be useful,
|
||
12 | * but WITHOUT ANY WARRANTY; without even the implied warranty of
|
||
13 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
||
14 | * GNU General Public License for more details.
|
||
15 | *
|
||
16 | * You should have received a copy of the GNU General Public License
|
||
17 | * along with this program; if not, write to the Free Software
|
||
18 | * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
|
||
19 | * MA 02110-1301, USA.
|
||
20 | *
|
||
21 | * For any additional information, do not hesitate to contact us
|
||
22 | * at info AT gvsig.com, or visit our website www.gvsig.com.
|
||
23 | */
|
||
24 | 43020 | jjdelcerro | package org.gvsig.fmap.dal.store.jdbc2.spi.operations; |
25 | |||
26 | import java.util.ArrayList; |
||
27 | 45712 | omartinez | import java.util.HashMap; |
28 | 43020 | jjdelcerro | import java.util.List; |
29 | 45166 | omartinez | import java.util.Map; |
30 | 43358 | jjdelcerro | import org.apache.commons.lang3.ArrayUtils; |
31 | 46324 | jjdelcerro | import org.apache.commons.lang3.BooleanUtils; |
32 | 43020 | jjdelcerro | import org.apache.commons.lang3.StringUtils; |
33 | 45155 | omartinez | import org.gvsig.expressionevaluator.Code; |
34 | 45162 | omartinez | import org.gvsig.expressionevaluator.Expression; |
35 | 44198 | jjdelcerro | import org.gvsig.expressionevaluator.ExpressionBuilder; |
36 | 46104 | omartinez | import static org.gvsig.expressionevaluator.ExpressionBuilder.FUNCTION_LET; |
37 | 44727 | jjdelcerro | import org.gvsig.expressionevaluator.ExpressionUtils; |
38 | 46401 | jjdelcerro | import org.gvsig.fmap.dal.SQLBuilder; |
39 | import org.gvsig.fmap.dal.SQLBuilder.OrderByBuilder; |
||
40 | 44682 | jjdelcerro | import org.gvsig.fmap.dal.SQLBuilder.SelectBuilder; |
41 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.exception.DataException; |
42 | 45162 | omartinez | import org.gvsig.fmap.dal.expressionevaluator.FeatureAttributeEmulatorExpression; |
43 | import org.gvsig.fmap.dal.feature.EditableFeatureAttributeDescriptor; |
||
44 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
45 | 45999 | omartinez | import org.gvsig.fmap.dal.feature.FeatureAttributeEmulator; |
46 | import org.gvsig.fmap.dal.feature.FeatureExtraColumns; |
||
47 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.feature.FeatureQuery; |
48 | import org.gvsig.fmap.dal.feature.FeatureQueryOrder; |
||
49 | 43026 | jjdelcerro | import org.gvsig.fmap.dal.feature.FeatureQueryOrder.FeatureQueryOrderMember; |
50 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.feature.FeatureType; |
51 | 46315 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.JDBCConnection; |
52 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
53 | 46324 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.JDBCServerExplorer; |
54 | import static org.gvsig.fmap.dal.store.jdbc2.JDBCServerExplorer.QUERY_DONT_ADD_PRIMARY_KEY_IN_THE_ORDER; |
||
55 | 44058 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference; |
56 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.ResulSetControler.ResultSetEntry; |
57 | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
||
58 | 44198 | jjdelcerro | import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_FEATURE_TYPE; |
59 | 46010 | jjdelcerro | import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_JDBCHELPER; |
60 | 46050 | omartinez | import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_QUERY; |
61 | 46010 | jjdelcerro | import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_SYMBOLTABLE; |
62 | 44198 | jjdelcerro | import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_TABLE; |
63 | 43020 | jjdelcerro | import org.gvsig.fmap.geom.DataTypes; |
64 | 46324 | jjdelcerro | import org.gvsig.tools.dataTypes.DataTypeUtils; |
65 | 45999 | omartinez | import org.gvsig.tools.dynobject.DynField; |
66 | 43020 | jjdelcerro | import org.gvsig.tools.evaluator.Evaluator; |
67 | 46105 | omartinez | import org.gvsig.tools.lang.CloneableUtils; |
68 | 43020 | jjdelcerro | |
69 | public class ResultSetForSetProviderOperation extends AbstractConnectionOperation { |
||
70 | 45709 | omartinez | |
71 | 44058 | jjdelcerro | private final TableReference table; |
72 | 43020 | jjdelcerro | private final String baseFilter; |
73 | private final String baseOrder; |
||
74 | private final FeatureType storeType; |
||
75 | private final FeatureType setType; |
||
76 | private final FeatureQuery query; |
||
77 | private final long limit; |
||
78 | private final long offset; |
||
79 | private final int fetchSize; |
||
80 | |||
81 | public ResultSetForSetProviderOperation(
|
||
82 | JDBCHelper helper, |
||
83 | 44058 | jjdelcerro | TableReference table, |
84 | 43020 | jjdelcerro | String baseFilter,
|
85 | String baseOrder,
|
||
86 | FeatureQuery query, |
||
87 | FeatureType storeType, |
||
88 | FeatureType setType, |
||
89 | long limit,
|
||
90 | long offset,
|
||
91 | int fetchSize
|
||
92 | 45709 | omartinez | ) { |
93 | 43020 | jjdelcerro | super(helper);
|
94 | this.table = table;
|
||
95 | this.baseFilter = baseFilter;
|
||
96 | this.baseOrder = baseOrder;
|
||
97 | this.storeType = storeType;
|
||
98 | this.setType = setType;
|
||
99 | this.query = query;
|
||
100 | this.limit = limit;
|
||
101 | this.offset = offset;
|
||
102 | 45709 | omartinez | this.fetchSize = fetchSize;
|
103 | 43020 | jjdelcerro | } |
104 | |||
105 | @Override
|
||
106 | 43377 | jjdelcerro | protected Object perform_operation() throws Exception { |
107 | 44678 | jjdelcerro | ResultSetEntry rs = createResultSet(); |
108 | 43020 | jjdelcerro | return rs;
|
109 | } |
||
110 | 45709 | omartinez | |
111 | 43377 | jjdelcerro | @Override
|
112 | 46315 | jjdelcerro | public Object perform(JDBCConnection conn) throws DataException { |
113 | 45709 | omartinez | throw new UnsupportedOperationException("Not supported yet."); |
114 | 43377 | jjdelcerro | } |
115 | 44678 | jjdelcerro | |
116 | public String getSQL() { |
||
117 | 43358 | jjdelcerro | List<FeatureAttributeDescriptor> columns = new ArrayList<>(); |
118 | 43020 | jjdelcerro | JDBCSQLBuilderBase sqlbuilder = createSQLBuilder(); |
119 | 44682 | jjdelcerro | String sql = this.getSQL(sqlbuilder, columns, null); |
120 | 44678 | jjdelcerro | return sql;
|
121 | } |
||
122 | 45709 | omartinez | |
123 | 44682 | jjdelcerro | public String getSQL( |
124 | 45709 | omartinez | JDBCSQLBuilderBase sqlbuilder, |
125 | List<FeatureAttributeDescriptor> columns,
|
||
126 | 44682 | jjdelcerro | List<String> extraColumnNames |
127 | 45709 | omartinez | ) { |
128 | 46050 | omartinez | double tolerance = -1; //query.getScale(); |
129 | 44198 | jjdelcerro | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
130 | 44682 | jjdelcerro | SelectBuilder select = sqlbuilder.select(); |
131 | 46163 | jjdelcerro | ArrayList<ExpressionBuilder.Value> valuesToRemoveFeatureType = new ArrayList<>(); |
132 | |||
133 | if( shouldUseACustomSelect() ) {
|
||
134 | String sql = table.getSubquery();
|
||
135 | if( StringUtils.containsAny(sql, "${where_filter}", "${and_filter}") ) { |
||
136 | Evaluator filter = query.getFilter(); |
||
137 | if (filter != null) { |
||
138 | String sqlfilter = filter.getSQL();
|
||
139 | if (!StringUtils.isEmpty(sqlfilter)) {
|
||
140 | if (this.helper.supportFilter(this.storeType, filter)) { |
||
141 | select.where().set(expbuilder.toValue(sqlfilter)); |
||
142 | } |
||
143 | } |
||
144 | } |
||
145 | if (!StringUtils.isEmpty(baseFilter)) {
|
||
146 | select.where().and(expbuilder.toValue(baseFilter)); |
||
147 | } |
||
148 | } |
||
149 | if( StringUtils.containsAny(sql, "${order_by_orderspec}", "${comma_orderspec}") ) { |
||
150 | FeatureQueryOrder order = query.getOrder(); |
||
151 | if (order != null) { |
||
152 | for (FeatureQueryOrderMember member : order.members()) {
|
||
153 | String attrName = member.getAttributeName();
|
||
154 | ExpressionBuilder.Variable col = expbuilder.column(attrName); |
||
155 | select.order_by().value(col).ascending(member.getAscending()); |
||
156 | } |
||
157 | } |
||
158 | } |
||
159 | if( select.has_where() || select.has_order_by() ) {
|
||
160 | sqlbuilder.setProperties( |
||
161 | null,
|
||
162 | PROP_FEATURE_TYPE, this.storeType,
|
||
163 | PROP_TABLE, table, |
||
164 | PROP_SYMBOLTABLE, this.query==null? null:this.query.getSymbolTable(), |
||
165 | PROP_JDBCHELPER, this.helper,
|
||
166 | PROP_QUERY, this.query
|
||
167 | ); |
||
168 | for (ExpressionBuilder.Value value : valuesToRemoveFeatureType) {
|
||
169 | value.setProperty(PROP_FEATURE_TYPE, null);
|
||
170 | } |
||
171 | if( select.has_where()) {
|
||
172 | String s = select.where().toString();
|
||
173 | sql = StringUtils.replace(sql, "${where_filter}", "WHERE " + s); |
||
174 | sql = StringUtils.replace(sql, "${and_filter}", "AND (" + s + ")"); |
||
175 | } |
||
176 | if( select.has_order_by() ) {
|
||
177 | String s = select.order_by().toString();
|
||
178 | sql = StringUtils.replace(sql, "${order_by_orderspec}", "ORDER BY " + s); |
||
179 | sql = StringUtils.replace(sql, "${comma_orderspec}", ", "+ s); |
||
180 | } |
||
181 | } |
||
182 | for (FeatureAttributeDescriptor attr : storeType) {
|
||
183 | columns.add(attr); |
||
184 | } |
||
185 | return sql;
|
||
186 | } |
||
187 | |||
188 | 45709 | omartinez | |
189 | 45712 | omartinez | Map<String, EditableFeatureAttributeDescriptor> allExtraColumns = new HashMap<>(); |
190 | for (EditableFeatureAttributeDescriptor column : this.setType.getExtraColumns().getColumns()) { |
||
191 | allExtraColumns.put(column.getName(), column); |
||
192 | } |
||
193 | 45718 | fdiaz | if(query != null){ |
194 | for (EditableFeatureAttributeDescriptor column : this.query.getExtraColumn().getColumns()) { |
||
195 | allExtraColumns.put(column.getName(), column); |
||
196 | } |
||
197 | 45712 | omartinez | } |
198 | |||
199 | 43020 | jjdelcerro | List<String> primaryKeys = new ArrayList<>(); |
200 | 45709 | omartinez | for (FeatureAttributeDescriptor attr : storeType.getPrimaryKey()) {
|
201 | 43020 | jjdelcerro | primaryKeys.add(attr.getName()); |
202 | } |
||
203 | 43706 | jjdelcerro | List<String> forcedColumns = new ArrayList<>(primaryKeys); |
204 | |||
205 | 43358 | jjdelcerro | String[] constantsAttributeNames = null; |
206 | 45709 | omartinez | if (query != null && query.hasConstantsAttributeNames()) { |
207 | 43358 | jjdelcerro | constantsAttributeNames = query.getConstantsAttributeNames(); |
208 | } |
||
209 | 45709 | omartinez | for (FeatureAttributeDescriptor attr : setType) {
|
210 | if (attr.isComputed()) {
|
||
211 | 46221 | omartinez | // Campos calculados se a?aden despues para asegurarnos que
|
212 | // primeros se a?aden los campos reales y al final los calculados
|
||
213 | continue;
|
||
214 | } |
||
215 | if (ArrayUtils.contains(constantsAttributeNames, attr.getName())) {
|
||
216 | continue;
|
||
217 | } |
||
218 | if (attr.isPrimaryKey()) {
|
||
219 | forcedColumns.remove(attr.getName()); |
||
220 | } |
||
221 | 46248 | jjdelcerro | if (query != null && (query.hasGroupByColumns() || query.hasAggregateFunctions()) ) { |
222 | 46221 | omartinez | String aggregate = query.getAggregate(this.table.getTable(), attr.getName()); |
223 | if (this.query.isAGroupByColumn(attr.getName())) { |
||
224 | select.column().name(attr.getName()); |
||
225 | } else if (aggregate == null) { |
||
226 | select.column().value(expbuilder.constant(null)).as(attr.getName());
|
||
227 | } else {
|
||
228 | select.column() |
||
229 | .value(ExpressionUtils.compile(aggregate).toValue(expbuilder)) |
||
230 | .as(attr.getName()); |
||
231 | } |
||
232 | } else {
|
||
233 | if (attr.getType() == DataTypes.GEOMETRY) {
|
||
234 | select.column().name(attr.getName()).as_geometry(); |
||
235 | // if( tolerance<=0 || !sqlbuilder.getConfig().has_functionality(Config.ST_Simplify)) {
|
||
236 | // select.column().name(attr.getName()).as_geometry();
|
||
237 | // } else {
|
||
238 | // select.column().value(
|
||
239 | // sqlbuilder.ST_Simplify(
|
||
240 | // sqlbuilder.column(attr.getName()),
|
||
241 | // sqlbuilder.constant(tolerance)
|
||
242 | // )
|
||
243 | // ).as_geometry();
|
||
244 | // }
|
||
245 | } else {
|
||
246 | select.column().name(attr.getName()); |
||
247 | } |
||
248 | } |
||
249 | columns.add(attr); |
||
250 | } |
||
251 | |||
252 | if (query == null || !query.hasGroupByColumns()) { |
||
253 | for (String attrName : forcedColumns) { |
||
254 | select.column().name(attrName); |
||
255 | FeatureAttributeDescriptor attr = setType.getAttributeDescriptor(attrName); |
||
256 | // Ojo, que cuando estamos pintando una vista NO TENEMOS LA PK y attr vale null!!!!
|
||
257 | // No se si deberiamos forzar a que cuando se crea un subfeaturetype se meta siempre
|
||
258 | // la pk, o simplemente nunca asumir que la vamos a tener.
|
||
259 | if (attr != null) { |
||
260 | columns.add(attr); |
||
261 | } |
||
262 | } |
||
263 | } |
||
264 | |||
265 | // Despues de a?adir los campos reales, se a?aden los campos calculados
|
||
266 | for (FeatureAttributeDescriptor attr : setType) {
|
||
267 | if (attr.isComputed()) {
|
||
268 | 45999 | omartinez | if (attr.getRelationType() == DynField.RELATION_TYPE_NONE) {
|
269 | FeatureAttributeEmulator attrEmulator = attr.getFeatureAttributeEmulator(); |
||
270 | if (attrEmulator instanceof FeatureAttributeEmulatorExpression) { |
||
271 | FeatureAttributeEmulatorExpression x = (FeatureAttributeEmulatorExpression) attrEmulator; |
||
272 | Expression exp = x.getExpression();
|
||
273 | 46050 | omartinez | |
274 | if (query != null && query.hasGroupByColumns()) { |
||
275 | String aggregate = query.getAggregate(this.table.getTable(), attr.getName()); |
||
276 | if (this.query.isAGroupByColumn(attr.getName())) { |
||
277 | if (!select.has_column(attr.getName())) {
|
||
278 | select.column().value(exp.getCode().toValue()).as(attr.getName()); |
||
279 | } |
||
280 | if (extraColumnNames != null && !extraColumnNames.contains(attr.getName())) { |
||
281 | extraColumnNames.add(attr.getName()); |
||
282 | } |
||
283 | } else if (aggregate == null) { |
||
284 | select.column().value(expbuilder.constant(null)).as(attr.getName());
|
||
285 | 46229 | omartinez | |
286 | if (extraColumnNames != null && !extraColumnNames.contains(attr.getName())) { |
||
287 | extraColumnNames.add(attr.getName()); |
||
288 | } |
||
289 | 46050 | omartinez | } else {
|
290 | String fn = this.query.getAggregateFunctions().get(attr.getName()); |
||
291 | ExpressionBuilder.Function aggregateExp = expbuilder.function(fn, exp.getCode().toValue()); |
||
292 | if (!select.has_column(attr.getName())) {
|
||
293 | select.column().value(aggregateExp).as(attr.getName()); |
||
294 | } |
||
295 | if (extraColumnNames != null && !extraColumnNames.contains(attr.getName())) { |
||
296 | extraColumnNames.add(attr.getName()); |
||
297 | } |
||
298 | 45999 | omartinez | } |
299 | 46050 | omartinez | } else {
|
300 | if (exp != null && !exp.isEmpty() && this.helper.supportExpression(setType, exp.getPhrase())) { |
||
301 | Code code = exp.getCode(); |
||
302 | select.column() |
||
303 | .value(code.toValue(expbuilder)) |
||
304 | .as(attr.getName()); |
||
305 | if (extraColumnNames != null && !extraColumnNames.contains(attr.getName())) { |
||
306 | extraColumnNames.add(attr.getName()); |
||
307 | } |
||
308 | } |
||
309 | |||
310 | 45999 | omartinez | } |
311 | } |
||
312 | } |
||
313 | 44324 | jjdelcerro | } |
314 | 43020 | jjdelcerro | } |
315 | 46221 | omartinez | |
316 | 46248 | jjdelcerro | if (query != null && (query.hasAggregateFunctions() || query.hasGroupByColumns())) { |
317 | // Hay que tener en cuenta que puede haber un MAX(CAMPO) sin que se especifique un GROUP BY
|
||
318 | if (query.hasAggregateFunctions() ) {
|
||
319 | for (Map.Entry<String, String> entry : query.getAggregateFunctions().entrySet()) { |
||
320 | Expression exp;
|
||
321 | FeatureAttributeDescriptor attr = allExtraColumns.get(entry.getKey()); |
||
322 | if (attr == null) { |
||
323 | attr = this.setType.getAttributeDescriptorFromAll(entry.getKey());
|
||
324 | } |
||
325 | if (attr == null) { |
||
326 | exp = ExpressionUtils.createExpression(entry.getKey()); |
||
327 | Code code = exp.getCode(); |
||
328 | if (!(code instanceof Code.Callable)) { |
||
329 | throw new RuntimeException("Not able to use aggregate function with this expression(1): " + entry.getKey()); |
||
330 | } |
||
331 | Code.Callable callable = (Code.Callable) code; |
||
332 | if (!callable.name().equalsIgnoreCase(FUNCTION_LET)) {
|
||
333 | throw new RuntimeException("Not able to use aggregate function with this expression(2): " + entry.getKey()); |
||
334 | } |
||
335 | String name = ((Code.Identifier) callable.parameters().get(0)).name(); |
||
336 | ExpressionBuilder.Value aggregate = callable.parameters().get(1).toValue();
|
||
337 | 46104 | omartinez | |
338 | 46248 | jjdelcerro | ExpressionBuilder.Function aggregateExp = expbuilder.function(entry.getValue(), aggregate); |
339 | 46104 | omartinez | |
340 | 46248 | jjdelcerro | select.remove_column(name); |
341 | select.column().value(aggregateExp).as(name); |
||
342 | } else if( attr.getFeatureAttributeEmulator()!=null ) { |
||
343 | exp = ((FeatureAttributeEmulatorExpression) attr.getFeatureAttributeEmulator()).getExpression(); |
||
344 | ExpressionBuilder.Function aggregateExp = expbuilder.function(entry.getValue(), exp.getCode().toValue()); |
||
345 | if (!select.has_column(attr.getName())) {
|
||
346 | select.column().value(aggregateExp).as(attr.getName()); |
||
347 | 46104 | omartinez | } |
348 | 46248 | jjdelcerro | if (extraColumnNames != null && !extraColumnNames.contains(attr.getName())) { |
349 | extraColumnNames.add(attr.getName()); |
||
350 | 46104 | omartinez | } |
351 | } |
||
352 | 45709 | omartinez | } |
353 | 45166 | omartinez | } |
354 | 46248 | jjdelcerro | if (query.hasGroupByColumns()) { // if tiene agrupacion |
355 | for (String attrName : query.getGroupByColumns()) { |
||
356 | if (allExtraColumns.get(attrName) != null) { //from setType and query |
||
357 | EditableFeatureAttributeDescriptor attr = allExtraColumns.get(attrName); |
||
358 | ExpressionBuilder.Variable col = expbuilder.column(attrName); |
||
359 | select.group_by(col); |
||
360 | Expression exp = ((FeatureAttributeEmulatorExpression) attr.getFeatureAttributeEmulator()).getExpression();
|
||
361 | if (!select.has_column(attrName)) {
|
||
362 | select.column().value(exp.getCode().toValue()).as(attrName); |
||
363 | } |
||
364 | if (extraColumnNames!=null && !extraColumnNames.contains(attr.getName())) { |
||
365 | extraColumnNames.add(attrName); |
||
366 | } |
||
367 | } else if (setType.get(attrName) != null && setType.getAttributeDescriptor(attrName).isComputed()) { |
||
368 | FeatureAttributeDescriptor attr = setType.getAttributeDescriptor(attrName); |
||
369 | ExpressionBuilder.Variable col = expbuilder.column(attrName); |
||
370 | select.group_by(col); |
||
371 | Expression exp = ((FeatureAttributeEmulatorExpression) attr.getFeatureAttributeEmulator()).getExpression();
|
||
372 | if (!select.has_column(attrName)) {
|
||
373 | select.column().value(exp.getCode().toValue()).as(attrName); |
||
374 | } |
||
375 | if (extraColumnNames!=null && !extraColumnNames.contains(attr.getName())) { |
||
376 | extraColumnNames.add(attrName); |
||
377 | } |
||
378 | } else if (setType.get(attrName) == null) { |
||
379 | try {
|
||
380 | Code code = ExpressionUtils.compile(attrName); |
||
381 | select.group_by(code.toValue()); |
||
382 | } catch (Exception ex) { |
||
383 | throw new RuntimeException("Not able to create column by expression in groupby query", ex); |
||
384 | } |
||
385 | } else {
|
||
386 | ExpressionBuilder.Function atrcolumn = expbuilder.getattr(this.table.getTable(), attrName);
|
||
387 | select.group_by(atrcolumn); |
||
388 | 45166 | omartinez | } |
389 | 45155 | omartinez | } |
390 | 45709 | omartinez | } |
391 | 44854 | omartinez | } else {
|
392 | 46014 | jjdelcerro | if (this.query != null) { |
393 | FeatureExtraColumns extraColumns = this.query.getExtraColumn();
|
||
394 | if (extraColumns != null && !extraColumns.isEmpty()) { |
||
395 | for (EditableFeatureAttributeDescriptor attr : extraColumns.getColumns()) {
|
||
396 | if (!attr.isComputed()) {
|
||
397 | continue;
|
||
398 | } |
||
399 | FeatureAttributeEmulator attrEmulator = attr.getFeatureAttributeEmulator(); |
||
400 | if (attrEmulator instanceof FeatureAttributeEmulatorExpression) { |
||
401 | FeatureAttributeEmulatorExpression x = (FeatureAttributeEmulatorExpression) attrEmulator; |
||
402 | Expression exp = x.getExpression();
|
||
403 | if (exp != null && !exp.isEmpty() && this.helper.supportExpression(setType, exp.getPhrase())) { |
||
404 | Code code = exp.getCode(); |
||
405 | select.column() |
||
406 | .value(code.toValue(expbuilder)) |
||
407 | .as(attr.getName()); |
||
408 | 46050 | omartinez | if (extraColumnNames!=null && !extraColumnNames.contains(attr.getName())) { |
409 | 46014 | jjdelcerro | extraColumnNames.add(attr.getName()); |
410 | } |
||
411 | 45999 | omartinez | } |
412 | } |
||
413 | } |
||
414 | } |
||
415 | 46014 | jjdelcerro | } |
416 | 43020 | jjdelcerro | } |
417 | 45709 | omartinez | |
418 | 44682 | jjdelcerro | select.from().table() |
419 | 44058 | jjdelcerro | .database(this.table.getDatabase())
|
420 | .schema(this.table.getSchema())
|
||
421 | .name(this.table.getTable());
|
||
422 | 44682 | jjdelcerro | select.from().subquery(this.table.getSubquery());
|
423 | 45709 | omartinez | |
424 | Evaluator filter = query == null ? null : query.getFilter(); |
||
425 | if (filter != null) { |
||
426 | 43020 | jjdelcerro | String sqlfilter = filter.getSQL();
|
427 | 45709 | omartinez | if (!StringUtils.isEmpty(sqlfilter)) {
|
428 | if (this.helper.supportFilter(this.storeType, filter)) { |
||
429 | 44682 | jjdelcerro | select.where().set(expbuilder.toValue(sqlfilter)); |
430 | 44198 | jjdelcerro | } |
431 | 43020 | jjdelcerro | } |
432 | } |
||
433 | 45709 | omartinez | if (!StringUtils.isEmpty(baseFilter)) {
|
434 | 44682 | jjdelcerro | select.where().and(expbuilder.toValue(baseFilter)); |
435 | 43020 | jjdelcerro | } |
436 | 45709 | omartinez | |
437 | 45708 | omartinez | FeatureQueryOrder order = query == null ? null : query.getOrder(); |
438 | if (order != null) { |
||
439 | for (FeatureQueryOrderMember member : order.members()) {
|
||
440 | 45712 | omartinez | String attrName = member.getAttributeName();
|
441 | 45708 | omartinez | if (member.hasEvaluator()) {
|
442 | 46401 | jjdelcerro | // Order-by una expression en el query
|
443 | 43020 | jjdelcerro | String sqlorder = member.getEvaluator().getSQL();
|
444 | 45708 | omartinez | select.order_by() |
445 | .value(expbuilder.toValue(sqlorder)) |
||
446 | .ascending(member.getAscending()); |
||
447 | } else {
|
||
448 | 46401 | jjdelcerro | int nullsMode = OrderByBuilder.MODE_NULLS_LAST;
|
449 | 45712 | omartinez | if (allExtraColumns.get(attrName) != null) { |
450 | 46401 | jjdelcerro | // Order-by por una columna extra
|
451 | 45712 | omartinez | Expression exp = ((FeatureAttributeEmulatorExpression) allExtraColumns.get(attrName).getFeatureAttributeEmulator()).getExpression();
|
452 | if (!select.has_column(attrName)) {
|
||
453 | select.column().value(exp.getCode().toValue()).as(attrName); |
||
454 | } |
||
455 | 46050 | omartinez | if (extraColumnNames!=null && !extraColumnNames.contains(attrName)) { |
456 | 45712 | omartinez | extraColumnNames.add(attrName); |
457 | } |
||
458 | } else if (setType.get(attrName) != null && setType.getAttributeDescriptor(attrName).isComputed()) { |
||
459 | 46401 | jjdelcerro | // Order-by por un campo calculado del set/store
|
460 | 45712 | omartinez | Expression exp = ((FeatureAttributeEmulatorExpression) setType.getAttributeDescriptor(attrName).getFeatureAttributeEmulator()).getExpression();
|
461 | if (!select.has_column(attrName)) {
|
||
462 | select.column().value(exp.getCode().toValue()).as(attrName); |
||
463 | } |
||
464 | 46050 | omartinez | if (extraColumnNames!=null && !extraColumnNames.contains(attrName)) { |
465 | 45712 | omartinez | extraColumnNames.add(attrName); |
466 | } |
||
467 | 46401 | jjdelcerro | } else {
|
468 | // Order-by por un campo del store
|
||
469 | FeatureAttributeDescriptor attr = storeType.getAttributeDescriptor(attrName); |
||
470 | if( attr!=null && (!attr.allowNull() || attr.isPrimaryKey()) ) { |
||
471 | // Puede ser costoso ordenar los nulos al principio o final, asi que
|
||
472 | // si el campo no admite nulos pasamos de indicarselo.
|
||
473 | nullsMode = OrderByBuilder.MODE_NULLS_NOT_SPECIFIED; |
||
474 | } |
||
475 | 43020 | jjdelcerro | } |
476 | 46163 | jjdelcerro | ExpressionBuilder.Variable col = expbuilder.column(attrName); |
477 | 46104 | omartinez | |
478 | 46401 | jjdelcerro | // En el OrderBy no queremos que se sustituya el nombre del campo calculado
|
479 | 46163 | jjdelcerro | // por su expresion. Se encarga el formater y lo evitamos quitandole el ftype
|
480 | // al value.
|
||
481 | valuesToRemoveFeatureType.add(col); |
||
482 | 46401 | jjdelcerro | select.order_by().value(col).ascending(member.getAscending()).nulls(nullsMode); |
483 | 43020 | jjdelcerro | } |
484 | } |
||
485 | } |
||
486 | 45709 | omartinez | |
487 | if (!StringUtils.isEmpty(baseOrder)) {
|
||
488 | 44682 | jjdelcerro | select.order_by().custom(baseOrder); |
489 | 43020 | jjdelcerro | } |
490 | 45709 | omartinez | |
491 | 46105 | omartinez | if (select.has_group_by()) { // && isPaginated()) { |
492 | // Cuando paginamos debemos ordenar por las columnas del groupby.
|
||
493 | // Ordenamos siempre para obtener el mismo resultado cuando paginamos
|
||
494 | // y no paginamos.
|
||
495 | for (ExpressionBuilder.Value group : select.getGroups()) {
|
||
496 | if (select.getOrderBy(group) == null) { |
||
497 | ExpressionBuilder.Value v = (ExpressionBuilder.Value) CloneableUtils.cloneQuietly(group); |
||
498 | 46401 | jjdelcerro | select.order_by().value(v).ascending().nulls(OrderByBuilder.MODE_NULLS_LAST); |
499 | 46105 | omartinez | valuesToRemoveFeatureType.add(v); |
500 | 45709 | omartinez | } |
501 | } |
||
502 | 46105 | omartinez | } |
503 | |||
504 | if (primaryKeys.isEmpty()) {
|
||
505 | // Muy probablemente si no tiene pk sea una vista, asi que
|
||
506 | // pasaremos de ordenar y esperemos que la vista este ya ordenada.
|
||
507 | select.disable_check_order_and_offset(); |
||
508 | } else {
|
||
509 | // Siempre ordenamos por la clave primaria
|
||
510 | 46324 | jjdelcerro | Object x = query==null? null:query.getQueryParameter(QUERY_DONT_ADD_PRIMARY_KEY_IN_THE_ORDER); |
511 | 46325 | jjdelcerro | if( x==null || !DataTypeUtils.toBoolean(x) ) { |
512 | 46324 | jjdelcerro | for (String attrName : primaryKeys) { |
513 | if (select.getOrderBy(attrName) == null) { |
||
514 | 46401 | jjdelcerro | select.order_by() |
515 | .column(attrName) |
||
516 | .ascending() |
||
517 | .nulls(OrderByBuilder.MODE_NULLS_NOT_SPECIFIED) |
||
518 | ; |
||
519 | // No tengo claro que pasa si se esta agrupando, y no se ha
|
||
520 | // incluido en el agrupamiento al PK. En ese caso el select
|
||
521 | // que se genera tendria una pinta tal que asi:
|
||
522 | // SELECT NULL as pk, ... ORDER BY pk ASC
|
||
523 | // Probablemente en ese caso no tendriamos que meter la PK
|
||
524 | // en el order-by ya que creo que no cambiaria el resultado.
|
||
525 | 46324 | jjdelcerro | } |
526 | 46105 | omartinez | } |
527 | 45989 | fdiaz | } |
528 | } |
||
529 | 46105 | omartinez | |
530 | 45709 | omartinez | if (limit > 0) { |
531 | 44682 | jjdelcerro | select.limit(limit); |
532 | 43020 | jjdelcerro | } else {
|
533 | 45709 | omartinez | select.limit(query == null ? null : query.getLimit()); |
534 | 43020 | jjdelcerro | } |
535 | 45709 | omartinez | if (offset > 0) { |
536 | 44682 | jjdelcerro | select.offset(offset); |
537 | 45709 | omartinez | } |
538 | 44198 | jjdelcerro | sqlbuilder.setProperties( |
539 | 45709 | omartinez | null,
|
540 | 44198 | jjdelcerro | PROP_FEATURE_TYPE, this.storeType,
|
541 | 46010 | jjdelcerro | PROP_TABLE, table, |
542 | PROP_SYMBOLTABLE, this.query==null? null:this.query.getSymbolTable(), |
||
543 | 46050 | omartinez | PROP_JDBCHELPER, this.helper,
|
544 | PROP_QUERY, this.query
|
||
545 | 45709 | omartinez | ); |
546 | 46050 | omartinez | for (ExpressionBuilder.Value value : valuesToRemoveFeatureType) {
|
547 | value.setProperty(PROP_FEATURE_TYPE, null);
|
||
548 | } |
||
549 | 46105 | omartinez | this.helper.expandCalculedColumns(sqlbuilder);
|
550 | 44748 | jjdelcerro | this.helper.processSpecialFunctions(sqlbuilder, storeType, extraColumnNames);
|
551 | 43020 | jjdelcerro | String sql = sqlbuilder.toString();
|
552 | 44678 | jjdelcerro | return sql;
|
553 | } |
||
554 | 46105 | omartinez | |
555 | private boolean isPaginated() { |
||
556 | // No tengo claro que (offset==0 && limit>0) sea lo mas correcto,
|
||
557 | // Pero cuando se va a paginar y se pide la primera pagina offset es
|
||
558 | // 0 y limit>0, y si no ordenamos ya esa primera pagina los resultados
|
||
559 | // que se obtienen no son correctos, ya que la primera pagina se saca
|
||
560 | // sin ordenar y el resto ordenadas.
|
||
561 | // Probablemente deberiamos tener alguna otra forma de detectar que
|
||
562 | // estamos paginanado ya que asi no distinguimo si solo queremos
|
||
563 | // obtener los primeros elementos sin importarnos su orden.
|
||
564 | return (offset > 0 || (offset == 0 && limit > 0)); |
||
565 | } |
||
566 | 45709 | omartinez | |
567 | 44678 | jjdelcerro | public ResultSetEntry createResultSet() throws DataException { |
568 | List<FeatureAttributeDescriptor> columns = new ArrayList<>(); |
||
569 | 44682 | jjdelcerro | List<String> extraColumnNames = new ArrayList<>(); |
570 | |||
571 | 44678 | jjdelcerro | JDBCSQLBuilderBase sqlbuilder = createSQLBuilder(); |
572 | 44682 | jjdelcerro | String sql = this.getSQL(sqlbuilder, columns, extraColumnNames); |
573 | 45709 | omartinez | |
574 | 43020 | jjdelcerro | ResultSetEntry resultSetEntry = this.helper.getResulSetControler().create(
|
575 | 45709 | omartinez | sql, fetchSize, |
576 | 44376 | jjdelcerro | columns.toArray(new FeatureAttributeDescriptor[columns.size()]),
|
577 | 44682 | jjdelcerro | extraColumnNames.toArray(new String[extraColumnNames.size()]) |
578 | 43020 | jjdelcerro | ); |
579 | return resultSetEntry;
|
||
580 | } |
||
581 | 45709 | omartinez | |
582 | 46163 | jjdelcerro | private boolean shouldUseACustomSelect() { |
583 | if( !table.hasSubquery() ) {
|
||
584 | return false; |
||
585 | } |
||
586 | if( this.query == null ) { |
||
587 | return false; |
||
588 | } |
||
589 | if( this.query.isUseSubquery() ) { |
||
590 | return false; |
||
591 | } |
||
592 | if( this.query.hasGroupByColumns() ) { |
||
593 | return false; |
||
594 | } |
||
595 | if( this.query.hasAggregateFunctions() ) { |
||
596 | return false; |
||
597 | } |
||
598 | if( !this.query.hasFilter() && !this.query.hasOrder() ) { |
||
599 | return true; |
||
600 | } |
||
601 | // si el filtro es incompatible con la BBDD
|
||
602 | // return false
|
||
603 | // si el orden es incompatible con la BBDD
|
||
604 | // return false
|
||
605 | // si filtro u orden usan la funcion foreing_value
|
||
606 | // return false;
|
||
607 | return true; |
||
608 | } |
||
609 | 43020 | jjdelcerro | } |