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 @ 47779
History | View | Annotate | Download (28.5 KB)
1 | 47539 | jjdelcerro | 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 | 47580 | fdiaz | filter.append(" SELECT \"ISO2\" FROM countries");
|
562 | 47539 | jjdelcerro | 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 | 47580 | fdiaz | filter.append(" SELECT \"ISO2\" FROM countries");
|
660 | 47539 | jjdelcerro | 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 | } |