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