svn-gvsig-desktop / trunk / org.gvsig.desktop / org.gvsig.desktop.plugin / org.gvsig.h2spatial / org.gvsig.h2spatial.h2gis132 / org.gvsig.h2spatial.h2gis132.provider / src / test / java / org / gvsig / fmap / dal / store / h2 / operations / sql / TestResultSetForSetProvider.java @ 46543
History | View | Annotate | Download (28.6 KB)
1 |
package org.gvsig.fmap.dal.store.h2.operations.sql; |
---|---|
2 |
|
3 |
import java.util.List; |
4 |
import junit.framework.TestCase; |
5 |
import org.gvsig.expressionevaluator.ExpressionUtils; |
6 |
import org.gvsig.fmap.dal.DALLocator; |
7 |
import org.gvsig.fmap.dal.DataManager; |
8 |
import org.gvsig.fmap.dal.DataTypes; |
9 |
import org.gvsig.fmap.dal.feature.EditableFeatureAttributeDescriptor; |
10 |
import org.gvsig.fmap.dal.feature.EditableFeatureType; |
11 |
import org.gvsig.fmap.dal.feature.FeatureQuery; |
12 |
import org.gvsig.fmap.dal.feature.FeatureStore; |
13 |
import org.gvsig.fmap.dal.feature.FeatureType; |
14 |
import org.gvsig.fmap.dal.impl.expressionevaluator.DefaultFeatureAttributeEmulatorExpression; |
15 |
import org.gvsig.fmap.dal.store.h2.TestUtils; |
16 |
import org.gvsig.fmap.dal.store.h2.TestUtilsH2Spatial; |
17 |
import org.gvsig.fmap.dal.store.jdbc2.AbstractTestUtils; |
18 |
import org.gvsig.fmap.dal.store.jdbc2.AbstractTestUtils.Expecteds; |
19 |
import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
20 |
import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory; |
21 |
import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference; |
22 |
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
23 |
import org.gvsig.fmap.dal.store.jdbc2.spi.operations.ResultSetForSetProviderOperation; |
24 |
import org.gvsig.tools.library.impl.DefaultLibrariesInitializer; |
25 |
import org.slf4j.Logger; |
26 |
import org.slf4j.LoggerFactory; |
27 |
|
28 |
@SuppressWarnings({"UseSpecificCatch", "CallToPrintStackTrace"}) |
29 |
public class TestResultSetForSetProvider extends TestCase { |
30 |
|
31 |
private static final Logger LOGGER = LoggerFactory.getLogger(TestResultSetForSetProvider.class); |
32 |
|
33 |
public TestResultSetForSetProvider(String testName) { |
34 |
super(testName);
|
35 |
} |
36 |
|
37 |
@Override
|
38 |
protected void setUp() throws Exception { |
39 |
super.setUp();
|
40 |
new DefaultLibrariesInitializer().fullInitialize();
|
41 |
} |
42 |
|
43 |
@Override
|
44 |
protected void tearDown() throws Exception { |
45 |
super.tearDown();
|
46 |
} |
47 |
|
48 |
protected AbstractTestUtils utils;
|
49 |
|
50 |
public AbstractTestUtils utils() {
|
51 |
if (this.utils == null) { |
52 |
this.utils = this.createUtils(); |
53 |
} |
54 |
return this.utils; |
55 |
} |
56 |
|
57 |
protected AbstractTestUtils createUtils() {
|
58 |
return new TestUtilsH2Spatial(); |
59 |
} |
60 |
|
61 |
public void testSimple() throws Exception { |
62 |
JDBCHelper helper = TestUtils.createJDBCHelper(); |
63 |
JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
64 |
OperationsFactory operations = helper.getOperations(); |
65 |
|
66 |
Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
67 |
|
68 |
FeatureStore sourceStore = TestUtils.openSourceStore1(); |
69 |
|
70 |
TableReference table = operations.createTableReference( |
71 |
"dbtest",
|
72 |
sqlbuilder.default_schema(), |
73 |
"test",
|
74 |
null
|
75 |
); |
76 |
FeatureType featureType = sourceStore.getDefaultFeatureType(); |
77 |
ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
78 |
table, |
79 |
null,
|
80 |
null,
|
81 |
null,
|
82 |
featureType, |
83 |
featureType, |
84 |
0,
|
85 |
0,
|
86 |
0
|
87 |
); |
88 |
String sql = resultSetForSetProvider.getSQL();
|
89 |
utils().output_results("testSimple",sql,expectedSQLs.get("testSimple")); |
90 |
utils().runSQLToCheckSyntax("testSimple", sourceStore, table.getTable(), sql);
|
91 |
assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testSimple"), sql); |
92 |
} |
93 |
|
94 |
public void testComputedAttribute() throws Exception { |
95 |
try {
|
96 |
JDBCHelper helper = TestUtils.createJDBCHelper(); |
97 |
JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
98 |
OperationsFactory operations = helper.getOperations(); |
99 |
|
100 |
Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
101 |
|
102 |
FeatureStore sourceStore = TestUtils.openSourceStore1(); |
103 |
|
104 |
TableReference table = operations.createTableReference( |
105 |
"dbtest",
|
106 |
sqlbuilder.default_schema(), |
107 |
"test",
|
108 |
null
|
109 |
); |
110 |
FeatureType featureType = sourceStore.getDefaultFeatureType(); |
111 |
EditableFeatureType eFeatureType = featureType.getEditable(); |
112 |
eFeatureType.add("Compu1", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("ID*2"))); |
113 |
|
114 |
ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
115 |
table, |
116 |
null,
|
117 |
null,
|
118 |
null,
|
119 |
eFeatureType, |
120 |
eFeatureType, |
121 |
0,
|
122 |
0,
|
123 |
0
|
124 |
); |
125 |
String sql = resultSetForSetProvider.getSQL();
|
126 |
utils().output_results("testComputedAttribute",sql,expectedSQLs.get("testComputedAttribute")); |
127 |
utils().runSQLToCheckSyntax("testComputedAttribute", sourceStore, table.getTable(), sql);
|
128 |
assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testComputedAttribute"), sql); |
129 |
} catch (Exception ex) { |
130 |
ex.printStackTrace(); |
131 |
throw ex;
|
132 |
} |
133 |
} |
134 |
|
135 |
public void testComputedAttribute2() throws Exception { |
136 |
try {
|
137 |
JDBCHelper helper = TestUtils.createJDBCHelper(); |
138 |
JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
139 |
OperationsFactory operations = helper.getOperations(); |
140 |
|
141 |
Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
142 |
|
143 |
FeatureStore sourceStore = TestUtils.openSourceStore1(); |
144 |
|
145 |
TableReference table = operations.createTableReference( |
146 |
"dbtest",
|
147 |
sqlbuilder.default_schema(), |
148 |
"test",
|
149 |
null
|
150 |
); |
151 |
FeatureType featureType = sourceStore.getDefaultFeatureType(); |
152 |
EditableFeatureType eFeatureType = featureType.getEditable(); |
153 |
eFeatureType.add("Compu1", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("ID*2"))); |
154 |
eFeatureType.add("Compu2", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+10+Compu1"))); |
155 |
|
156 |
ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
157 |
table, |
158 |
null,
|
159 |
null,
|
160 |
null,
|
161 |
eFeatureType, |
162 |
eFeatureType, |
163 |
0,
|
164 |
0,
|
165 |
0
|
166 |
); |
167 |
String sql = resultSetForSetProvider.getSQL();
|
168 |
utils().output_results("testComputedAttribute2", sql, expectedSQLs.get("testComputedAttribute2")); |
169 |
utils().runSQLToCheckSyntax("testComputedAttribute2", sourceStore, table.getTable(), sql);
|
170 |
assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testComputedAttribute2"), sql); |
171 |
} catch (Exception ex) { |
172 |
ex.printStackTrace(); |
173 |
throw ex;
|
174 |
} |
175 |
} |
176 |
|
177 |
public void testComputedExtraColumn() throws Exception { |
178 |
try {
|
179 |
JDBCHelper helper = TestUtils.createJDBCHelper(); |
180 |
JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
181 |
OperationsFactory operations = helper.getOperations(); |
182 |
|
183 |
Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
184 |
|
185 |
FeatureStore sourceStore = TestUtils.openSourceStore1(); |
186 |
|
187 |
TableReference table = operations.createTableReference( |
188 |
"dbtest",
|
189 |
sqlbuilder.default_schema(), |
190 |
"test",
|
191 |
null
|
192 |
); |
193 |
FeatureType featureType = sourceStore.getDefaultFeatureType(); |
194 |
EditableFeatureType eFeatureType = featureType.getEditable(); |
195 |
eFeatureType.add("Compu1", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("ID*2"))); |
196 |
|
197 |
FeatureQuery query = sourceStore.createFeatureQuery(); |
198 |
EditableFeatureAttributeDescriptor extraColumn1 = query.getExtraColumn().add("Extra1", DataTypes.INTEGER);
|
199 |
extraColumn1.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+10+Compu1"))); |
200 |
|
201 |
ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
202 |
table, |
203 |
null,
|
204 |
null,
|
205 |
query, |
206 |
eFeatureType, |
207 |
eFeatureType, |
208 |
0,
|
209 |
0,
|
210 |
0
|
211 |
); |
212 |
String sql = resultSetForSetProvider.getSQL();
|
213 |
utils().output_results("testComputedExtraColumn", sql, expectedSQLs.get("testComputedExtraColumn")); |
214 |
utils().runSQLToCheckSyntax("testComputedExtraColumn", sourceStore, table.getTable(), sql);
|
215 |
assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testComputedExtraColumn"), sql); |
216 |
} catch (Exception ex) { |
217 |
ex.printStackTrace(); |
218 |
throw ex;
|
219 |
} |
220 |
} |
221 |
|
222 |
public void testComputedExtraColumn2() throws Exception { |
223 |
try {
|
224 |
JDBCHelper helper = TestUtils.createJDBCHelper(); |
225 |
JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
226 |
OperationsFactory operations = helper.getOperations(); |
227 |
|
228 |
Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
229 |
|
230 |
FeatureStore sourceStore = TestUtils.openSourceStore1(); |
231 |
|
232 |
TableReference table = operations.createTableReference( |
233 |
"dbtest",
|
234 |
sqlbuilder.default_schema(), |
235 |
"test",
|
236 |
null
|
237 |
); |
238 |
FeatureType featureType = sourceStore.getDefaultFeatureType(); |
239 |
EditableFeatureType eFeatureType = featureType.getEditable(); |
240 |
|
241 |
FeatureQuery query = sourceStore.createFeatureQuery(); |
242 |
EditableFeatureAttributeDescriptor extraColumn1 = query.getExtraColumn().add("Extra1", DataTypes.INTEGER);
|
243 |
extraColumn1.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("ID*2"))); |
244 |
EditableFeatureAttributeDescriptor extraColumn2 = query.getExtraColumn().add("Extra2", DataTypes.INTEGER);
|
245 |
extraColumn2.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+10+Extra1"))); |
246 |
|
247 |
ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
248 |
table, |
249 |
null,
|
250 |
null,
|
251 |
query, |
252 |
eFeatureType, |
253 |
eFeatureType, |
254 |
0,
|
255 |
0,
|
256 |
0
|
257 |
); |
258 |
String sql = resultSetForSetProvider.getSQL();
|
259 |
utils().output_results("testComputedExtraColumn2", sql, expectedSQLs.get("testComputedExtraColumn2")); |
260 |
utils().runSQLToCheckSyntax("testComputedExtraColumn2", sourceStore, table.getTable(), sql);
|
261 |
assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testComputedExtraColumn2"), sql); |
262 |
} catch (Exception ex) { |
263 |
ex.printStackTrace(); |
264 |
throw ex;
|
265 |
} |
266 |
} |
267 |
|
268 |
public void testComputedExtraColumnWithWhere() throws Exception { |
269 |
try {
|
270 |
JDBCHelper helper = TestUtils.createJDBCHelper(); |
271 |
JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
272 |
OperationsFactory operations = helper.getOperations(); |
273 |
|
274 |
Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
275 |
|
276 |
FeatureStore sourceStore = TestUtils.openSourceStore1(); |
277 |
|
278 |
TableReference table = operations.createTableReference( |
279 |
"dbtest",
|
280 |
sqlbuilder.default_schema(), |
281 |
"test",
|
282 |
null
|
283 |
); |
284 |
|
285 |
StringBuilder filter = new StringBuilder(); |
286 |
filter.append("Extra1 > 10");
|
287 |
|
288 |
FeatureType featureType = sourceStore.getDefaultFeatureType(); |
289 |
|
290 |
EditableFeatureType eFeatureType = featureType.getEditable(); |
291 |
eFeatureType.add("Compu1", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("ID*2"))); |
292 |
|
293 |
FeatureQuery query = sourceStore.createFeatureQuery(); |
294 |
EditableFeatureAttributeDescriptor extraColumn1 = query.getExtraColumn().add("Extra1", DataTypes.INTEGER);
|
295 |
extraColumn1.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+10+Compu1"))); |
296 |
query.addFilter(filter.toString()); |
297 |
query.getOrder().add("Extra1");
|
298 |
|
299 |
ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
300 |
table, |
301 |
null,
|
302 |
null,
|
303 |
query, |
304 |
eFeatureType, |
305 |
eFeatureType, |
306 |
0,
|
307 |
0,
|
308 |
0
|
309 |
); |
310 |
String sql = resultSetForSetProvider.getSQL();
|
311 |
utils().output_results("testComputedExtraColumnWithWhere", sql, expectedSQLs.get("testComputedExtraColumnWithWhere")); |
312 |
utils().runSQLToCheckSyntax("testComputedExtraColumnWithWhere", sourceStore, table.getTable(), sql);
|
313 |
assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testComputedExtraColumnWithWhere"), sql); |
314 |
} catch (Exception ex) { |
315 |
ex.printStackTrace(); |
316 |
throw ex;
|
317 |
} |
318 |
} |
319 |
|
320 |
public void testSimpleGroup() throws Exception { |
321 |
try {
|
322 |
JDBCHelper helper = TestUtils.createJDBCHelper(); |
323 |
JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
324 |
OperationsFactory operations = helper.getOperations(); |
325 |
|
326 |
Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
327 |
|
328 |
FeatureStore sourceStore = TestUtils.openSourceStore1(); |
329 |
|
330 |
TableReference table = operations.createTableReference( |
331 |
"dbtest",
|
332 |
sqlbuilder.default_schema(), |
333 |
"test",
|
334 |
null
|
335 |
); |
336 |
FeatureType featureType = sourceStore.getDefaultFeatureType(); |
337 |
FeatureQuery query = sourceStore.createFeatureQuery(); |
338 |
query.getGroupByColumns().add("Long");
|
339 |
query.getAggregateFunctions().put("ID", "MIN"); |
340 |
query.getAggregateFunctions().put("Byte", "MAX"); |
341 |
query.getAggregateFunctions().put("Double", "SUM"); |
342 |
ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
343 |
table, |
344 |
null,
|
345 |
null,
|
346 |
query, |
347 |
featureType, |
348 |
featureType, |
349 |
0,
|
350 |
0,
|
351 |
0
|
352 |
); |
353 |
String sql = resultSetForSetProvider.getSQL();
|
354 |
utils().output_results("testSimpleGroup", sql, expectedSQLs.get("testSimpleGroup")); |
355 |
utils().runSQLToCheckSyntax("testSimpleGroup", sourceStore, table.getTable(), sql);
|
356 |
assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testSimpleGroup"), sql); |
357 |
} catch (Throwable th) { |
358 |
LOGGER.warn("",th);
|
359 |
throw th;
|
360 |
} |
361 |
} |
362 |
|
363 |
public void testSimpleAggregateAndOrder() throws Exception { |
364 |
try {
|
365 |
JDBCHelper helper = TestUtils.createJDBCHelper(); |
366 |
JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
367 |
OperationsFactory operations = helper.getOperations(); |
368 |
|
369 |
Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
370 |
|
371 |
FeatureStore sourceStore = TestUtils.openSourceStore1(); |
372 |
|
373 |
TableReference table = operations.createTableReference( |
374 |
"dbtest",
|
375 |
sqlbuilder.default_schema(), |
376 |
"test",
|
377 |
null
|
378 |
); |
379 |
FeatureType featureType = sourceStore.getDefaultFeatureType(); |
380 |
FeatureQuery query = sourceStore.createFeatureQuery(); |
381 |
|
382 |
EditableFeatureAttributeDescriptor extraColumn1 = query.getExtraColumn().add("Extra1", DataTypes.INTEGER);
|
383 |
extraColumn1.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(featureType, ExpressionUtils.createExpression("Long+10"))); |
384 |
EditableFeatureAttributeDescriptor extraColumn2 = query.getExtraColumn().add("Extra2", DataTypes.INTEGER);
|
385 |
extraColumn2.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(featureType, ExpressionUtils.createExpression("20+Byte"))); |
386 |
|
387 |
query.getAggregateFunctions().put("ID", "MIN"); |
388 |
query.getAggregateFunctions().put("Byte", "MAX"); |
389 |
query.getAggregateFunctions().put("Double", "SUM"); |
390 |
query.getAggregateFunctions().put("Extra1", "SUM"); |
391 |
query.getOrder().add("ID");
|
392 |
// query.getOrder().add("Long");
|
393 |
query.getOrder().add("Extra1");
|
394 |
|
395 |
// !OJO! No podemos ordenar por Extra2, da el error:
|
396 |
// Column """Byte""" must be in the GROUP BY list
|
397 |
// query.getOrder().add("Extra2");
|
398 |
ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
399 |
table, |
400 |
null,
|
401 |
null,
|
402 |
query, |
403 |
featureType, |
404 |
featureType, |
405 |
0,
|
406 |
0,
|
407 |
0
|
408 |
); |
409 |
String sql = resultSetForSetProvider.getSQL();
|
410 |
utils().output_results("testSimpleAggregateAndOrder", sql, expectedSQLs.get("testSimpleAggregateAndOrder")); |
411 |
utils().runSQLToCheckSyntax("testSimpleAggregateAndOrder", sourceStore, table.getTable(), sql);
|
412 |
assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testSimpleAggregateAndOrder"), sql); |
413 |
} catch (Throwable th) { |
414 |
LOGGER.warn("",th);
|
415 |
throw th;
|
416 |
} |
417 |
} |
418 |
|
419 |
public void testGroupByComputed() throws Exception { |
420 |
try {
|
421 |
JDBCHelper helper = TestUtils.createJDBCHelper(); |
422 |
JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
423 |
OperationsFactory operations = helper.getOperations(); |
424 |
|
425 |
Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
426 |
|
427 |
FeatureStore sourceStore = TestUtils.openSourceStore1(); |
428 |
|
429 |
TableReference table = operations.createTableReference( |
430 |
"dbtest",
|
431 |
sqlbuilder.default_schema(), |
432 |
"test",
|
433 |
null
|
434 |
); |
435 |
FeatureType featureType = sourceStore.getDefaultFeatureType(); |
436 |
FeatureQuery query = sourceStore.createFeatureQuery(); |
437 |
EditableFeatureType eFeatureType = featureType.getEditable(); |
438 |
eFeatureType.add("Compu1", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("ID*2"))); |
439 |
eFeatureType.add("Compu2", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+300"))); |
440 |
eFeatureType.add("Compu3", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("1"))); |
441 |
EditableFeatureAttributeDescriptor extraColumn1 = query.getExtraColumn().add("Extra1", DataTypes.INTEGER);
|
442 |
EditableFeatureAttributeDescriptor extraColumn2 = query.getExtraColumn().add("Extra2", DataTypes.INTEGER);
|
443 |
extraColumn1.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+10+Compu1"))); |
444 |
extraColumn2.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("20+Byte+Compu1"))); |
445 |
query.getGroupByColumns().add("Long");
|
446 |
query.getGroupByColumns().add("Extra1");
|
447 |
query.getGroupByColumns().add("Compu1");
|
448 |
query.getAggregateFunctions().put("ID", "MIN"); |
449 |
query.getAggregateFunctions().put("Byte", "MAX"); |
450 |
query.getAggregateFunctions().put("Double", "SUM"); |
451 |
query.getAggregateFunctions().put("Extra2", "SUM"); |
452 |
query.getAggregateFunctions().put("Compu2", "SUM"); |
453 |
query.getAggregateFunctions().put("Compu3", "SUM"); |
454 |
ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
455 |
table, |
456 |
null,
|
457 |
null,
|
458 |
query, |
459 |
eFeatureType, |
460 |
eFeatureType, |
461 |
0,
|
462 |
0,
|
463 |
0
|
464 |
); |
465 |
String sql = resultSetForSetProvider.getSQL();
|
466 |
utils().output_results("testGroupByComputed", sql, expectedSQLs.get("testGroupByComputed")); |
467 |
utils().runSQLToCheckSyntax("testGroupByComputed", sourceStore, table.getTable(), sql);
|
468 |
assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testGroupByComputed"), sql); |
469 |
} catch (Throwable th) { |
470 |
LOGGER.warn("", th);
|
471 |
throw th;
|
472 |
} |
473 |
} |
474 |
|
475 |
public void testGroupByAndOrderByComputed() throws Exception { |
476 |
try {
|
477 |
JDBCHelper helper = TestUtils.createJDBCHelper(); |
478 |
JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
479 |
OperationsFactory operations = helper.getOperations(); |
480 |
|
481 |
Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
482 |
|
483 |
FeatureStore sourceStore = TestUtils.openSourceStore1(); |
484 |
|
485 |
TableReference table = operations.createTableReference( |
486 |
"dbtest",
|
487 |
sqlbuilder.default_schema(), |
488 |
"test",
|
489 |
null
|
490 |
); |
491 |
FeatureType featureType = sourceStore.getDefaultFeatureType(); |
492 |
FeatureQuery query = sourceStore.createFeatureQuery(); |
493 |
EditableFeatureType eFeatureType = featureType.getEditable(); |
494 |
eFeatureType.add("Compu1", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("ID*2"))); |
495 |
eFeatureType.add("Compu2", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+300"))); |
496 |
eFeatureType.add("Compu3", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("1"))); |
497 |
|
498 |
EditableFeatureAttributeDescriptor extraColumn1 = query.getExtraColumn().add("Extra1", DataTypes.INTEGER);
|
499 |
extraColumn1.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+10+Compu1"))); |
500 |
|
501 |
EditableFeatureAttributeDescriptor extraColumn2 = query.getExtraColumn().add("Extra2", DataTypes.INTEGER);
|
502 |
extraColumn2.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("20+Byte+Compu1"))); |
503 |
|
504 |
EditableFeatureAttributeDescriptor extraColumn3 = query.getExtraColumn().add("Extra3", DataTypes.INTEGER);
|
505 |
extraColumn3.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+33"))); |
506 |
|
507 |
query.getGroupByColumns().add("Long");
|
508 |
query.getGroupByColumns().add("Extra1");
|
509 |
query.getGroupByColumns().add("Compu1");
|
510 |
query.getAggregateFunctions().put("ID", "MIN"); |
511 |
query.getAggregateFunctions().put("Byte", "MAX"); |
512 |
query.getAggregateFunctions().put("Double", "SUM"); |
513 |
query.getAggregateFunctions().put("Extra2", "SUM"); |
514 |
query.getAggregateFunctions().put("Compu2", "SUM"); |
515 |
query.getAggregateFunctions().put("Compu3", "SUM"); |
516 |
query.getOrder().add("Extra1");
|
517 |
query.getOrder().add("Extra2");
|
518 |
// query.getOrder().add("Extra3");
|
519 |
ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
520 |
table, |
521 |
null,
|
522 |
null,
|
523 |
query, |
524 |
eFeatureType, |
525 |
eFeatureType, |
526 |
0,
|
527 |
0,
|
528 |
0
|
529 |
); |
530 |
String sql = resultSetForSetProvider.getSQL();
|
531 |
utils().output_results("testGroupByAndOrderByComputed", sql, expectedSQLs.get("testGroupByAndOrderByComputed")); |
532 |
utils().runSQLToCheckSyntax("testGroupByAndOrderByComputed", sourceStore, table.getTable(), sql);
|
533 |
assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testGroupByAndOrderByComputed"), sql); |
534 |
} catch (Throwable th) { |
535 |
LOGGER.warn("", th);
|
536 |
throw th;
|
537 |
} |
538 |
} |
539 |
|
540 |
public void testSubselect() throws Exception { |
541 |
JDBCHelper helper = TestUtils.createJDBCHelper(); |
542 |
JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
543 |
OperationsFactory operations = helper.getOperations(); |
544 |
DataManager dataManager = DALLocator.getDataManager(); |
545 |
|
546 |
dataManager.getStoresRepository().remove("test");
|
547 |
dataManager.getStoresRepository().remove("countries");
|
548 |
|
549 |
|
550 |
Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
551 |
|
552 |
FeatureStore sourceStore = TestUtils.openSourceStore1(); |
553 |
|
554 |
TableReference table = operations.createTableReference( |
555 |
"dbtest",
|
556 |
sqlbuilder.default_schema(), |
557 |
"test",
|
558 |
null
|
559 |
); |
560 |
StringBuilder filter = new StringBuilder(); |
561 |
filter.append("EXISTS(");
|
562 |
filter.append(" SELECT \"ISO_A2\" FROM countries");
|
563 |
filter.append(" WHERE ");
|
564 |
filter.append(" test.STRING = countries.CONTINENT AND ");
|
565 |
filter.append(" countries.LASTCENSUS < 0 ");
|
566 |
filter.append(" LIMIT 1;, ");
|
567 |
filter.append(" 'EXISTS62a964cd7bc24f409b97c03b9170408d' ");
|
568 |
filter.append(")");
|
569 |
FeatureType featureType = sourceStore.getDefaultFeatureType(); |
570 |
FeatureQuery query = sourceStore.createFeatureQuery(); |
571 |
query.addFilter(filter.toString()); |
572 |
ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
573 |
table, |
574 |
null,
|
575 |
null,
|
576 |
query, |
577 |
featureType, |
578 |
featureType, |
579 |
0,
|
580 |
0,
|
581 |
0
|
582 |
); |
583 |
String sql = resultSetForSetProvider.getSQL();
|
584 |
utils().output_results("testSubselect", sql, expectedSQLs.get("testSubselect")); |
585 |
|
586 |
assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testSubselect"), sql); |
587 |
} |
588 |
|
589 |
public void testSubselect2() throws Exception { |
590 |
try {
|
591 |
JDBCHelper helper = TestUtils.createJDBCHelper(); |
592 |
JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
593 |
OperationsFactory operations = helper.getOperations(); |
594 |
DataManager dataManager = DALLocator.getDataManager(); |
595 |
|
596 |
Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
597 |
|
598 |
FeatureStore sourceStore = TestUtils.openSourceStore1(); |
599 |
dataManager.getStoresRepository().add(sourceStore.getName(), sourceStore); |
600 |
|
601 |
FeatureStore countriesStore = TestUtils.openCountriesStore(); |
602 |
dataManager.getStoresRepository().add(countriesStore.getName(), countriesStore); |
603 |
|
604 |
TableReference table = operations.createTableReference( |
605 |
"dbtest",
|
606 |
sqlbuilder.default_schema(), |
607 |
"test",
|
608 |
null
|
609 |
); |
610 |
StringBuilder filter = new StringBuilder(); |
611 |
filter.append("EXISTS(");
|
612 |
filter.append(" SELECT \"Long\" FROM countries");
|
613 |
filter.append(" WHERE ");
|
614 |
filter.append(" test.STRING = countries.CONTINENT AND ");
|
615 |
filter.append(" countries.LASTCENSUS < 0 ");
|
616 |
filter.append(" LIMIT 1;, ");
|
617 |
filter.append(" 'EXISTS62a964cd7bc24f409b97c03b9170408d' ");
|
618 |
filter.append(")");
|
619 |
FeatureType featureType = sourceStore.getDefaultFeatureType(); |
620 |
FeatureQuery query = sourceStore.createFeatureQuery(); |
621 |
query.addFilter(filter.toString()); |
622 |
ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
623 |
table, |
624 |
null,
|
625 |
null,
|
626 |
query, |
627 |
featureType, |
628 |
featureType, |
629 |
0,
|
630 |
0,
|
631 |
0
|
632 |
); |
633 |
String sql = resultSetForSetProvider.getSQL();
|
634 |
utils().output_results("testSubselect2", sql, expectedSQLs.get("testSubselect2")); |
635 |
|
636 |
assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testSubselect2"), sql); |
637 |
} catch (Exception ex) { |
638 |
LOGGER.warn("", ex);
|
639 |
throw ex;
|
640 |
} |
641 |
} |
642 |
|
643 |
public void testGroupAndSubselect() throws Exception { |
644 |
JDBCHelper helper = TestUtils.createJDBCHelper(); |
645 |
JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
646 |
OperationsFactory operations = helper.getOperations(); |
647 |
|
648 |
Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
649 |
|
650 |
FeatureStore sourceStore = TestUtils.openSourceStore1(); |
651 |
|
652 |
TableReference table = operations.createTableReference( |
653 |
"dbtest",
|
654 |
sqlbuilder.default_schema(), |
655 |
"test",
|
656 |
null
|
657 |
); |
658 |
StringBuilder filter = new StringBuilder(); |
659 |
filter.append("EXISTS(");
|
660 |
filter.append(" SELECT \"ISO_A2\" FROM countries");
|
661 |
filter.append(" WHERE ");
|
662 |
filter.append(" test.STRING = countries.CONTINENT AND ");
|
663 |
filter.append(" countries.LASTCENSUS < 0 ");
|
664 |
filter.append(" LIMIT 1;, ");
|
665 |
filter.append(" 'EXISTS62a964cd7bc24f409b97c03b9170408d' ");
|
666 |
filter.append(")");
|
667 |
FeatureType featureType = sourceStore.getDefaultFeatureType(); |
668 |
FeatureQuery query = sourceStore.createFeatureQuery(); |
669 |
query.getGroupByColumns().add("Long");
|
670 |
query.getAggregateFunctions().put("ID", "MIN"); |
671 |
query.getAggregateFunctions().put("Byte", "MAX"); |
672 |
query.getAggregateFunctions().put("Double", "SUM"); |
673 |
query.addFilter(filter.toString()); |
674 |
ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
675 |
table, |
676 |
null,
|
677 |
null,
|
678 |
query, |
679 |
featureType, |
680 |
featureType, |
681 |
0,
|
682 |
0,
|
683 |
0
|
684 |
); |
685 |
String sql = resultSetForSetProvider.getSQL();
|
686 |
utils().output_results("testGroupAndSubselect", sql, expectedSQLs.get("testGroupAndSubselect")); |
687 |
|
688 |
assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testGroupAndSubselect"), sql); |
689 |
} |
690 |
|
691 |
public void testConstantColumnPrimaryKey() throws Exception { |
692 |
JDBCHelper helper = TestUtils.createJDBCHelper(); |
693 |
JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
694 |
OperationsFactory operations = helper.getOperations(); |
695 |
|
696 |
Expecteds expectedSQLs = utils().getExpecteds("resultSetForSetProvider.sql");
|
697 |
|
698 |
FeatureStore sourceStore = TestUtils.openSourceStore1(); |
699 |
|
700 |
TableReference table = operations.createTableReference( |
701 |
"dbtest",
|
702 |
sqlbuilder.default_schema(), |
703 |
"test",
|
704 |
null
|
705 |
); |
706 |
// meterle como constantCOlumn ID
|
707 |
|
708 |
FeatureType featureType = sourceStore.getDefaultFeatureType(); |
709 |
FeatureQuery query = sourceStore.createFeatureQuery(); |
710 |
|
711 |
query.setConstantsAttributeNames(new String[]{"ID"}); |
712 |
|
713 |
ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
714 |
table, |
715 |
null,
|
716 |
null,
|
717 |
query, |
718 |
featureType, |
719 |
featureType, |
720 |
0,
|
721 |
0,
|
722 |
0
|
723 |
); |
724 |
String sql = resultSetForSetProvider.getSQL();
|
725 |
utils().output_results("testConstantColumnPrimaryKey", sql, expectedSQLs.get("testConstantColumnPrimaryKey")); |
726 |
utils().runSQLToCheckSyntax("testConstantColumnPrimaryKey", sourceStore, table.getTable(), sql);
|
727 |
assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get("testConstantColumnPrimaryKey"), sql); |
728 |
} |
729 |
|
730 |
// TODO: a?adir un test con where, group y order.
|
731 |
} |