Revision 46505 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
| TestResultSetForSetProvider.java | ||
|---|---|---|
| 343 | 343 |
} |
| 344 | 344 |
} |
| 345 | 345 |
|
| 346 |
public void testSimpleAggregateAndOrder() throws Exception {
|
|
| 347 |
try {
|
|
| 348 |
JDBCHelper helper = TestUtils.createJDBCHelper(); |
|
| 349 |
JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
|
| 350 |
OperationsFactory operations = helper.getOperations(); |
|
| 351 |
|
|
| 352 |
List<String> expectedSQLs = TestUtils.getSQLs("resultSetForSetProvider.sql");
|
|
| 353 |
|
|
| 354 |
FeatureStore sourceStore = TestUtils.openSourceStore1(); |
|
| 355 |
|
|
| 356 |
TableReference table = operations.createTableReference( |
|
| 357 |
"dbtest", |
|
| 358 |
sqlbuilder.default_schema(), |
|
| 359 |
"test", |
|
| 360 |
null |
|
| 361 |
); |
|
| 362 |
FeatureType featureType = sourceStore.getDefaultFeatureType(); |
|
| 363 |
FeatureQuery query = sourceStore.createFeatureQuery(); |
|
| 364 |
|
|
| 365 |
EditableFeatureAttributeDescriptor extraColumn1 = query.getExtraColumn().add("Extra1", DataTypes.INTEGER);
|
|
| 366 |
extraColumn1.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(featureType, ExpressionUtils.createExpression("Long+10")));
|
|
| 367 |
EditableFeatureAttributeDescriptor extraColumn2 = query.getExtraColumn().add("Extra2", DataTypes.INTEGER);
|
|
| 368 |
extraColumn2.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(featureType, ExpressionUtils.createExpression("20+Byte")));
|
|
| 369 |
|
|
| 370 |
query.getAggregateFunctions().put("ID", "MIN");
|
|
| 371 |
query.getAggregateFunctions().put("Byte", "MAX");
|
|
| 372 |
query.getAggregateFunctions().put("Double", "SUM");
|
|
| 373 |
query.getAggregateFunctions().put("Extra1", "SUM");
|
|
| 374 |
query.getOrder().add("ID");
|
|
| 375 |
query.getOrder().add("Long");
|
|
| 376 |
query.getOrder().add("Extra1");
|
|
| 377 |
query.getOrder().add("Extra2");
|
|
| 378 |
ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
|
| 379 |
table, |
|
| 380 |
null, |
|
| 381 |
null, |
|
| 382 |
query, |
|
| 383 |
featureType, |
|
| 384 |
featureType, |
|
| 385 |
0, |
|
| 386 |
0, |
|
| 387 |
0 |
|
| 388 |
); |
|
| 389 |
String sql = resultSetForSetProvider.getSQL(); |
|
| 390 |
System.out.println("###### SQL:" + sql);
|
|
| 391 |
System.out.println("###### EXP:" + expectedSQLs.get(12));
|
|
| 392 |
|
|
| 393 |
assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get(12), sql);
|
|
| 394 |
} catch (Throwable th) {
|
|
| 395 |
LOGGER.warn("",th);
|
|
| 396 |
throw th; |
|
| 397 |
} |
|
| 398 |
} |
|
| 399 |
|
|
| 346 | 400 |
public void testGroupByComputed() throws Exception {
|
| 347 | 401 |
try {
|
| 348 | 402 |
JDBCHelper helper = TestUtils.createJDBCHelper(); |
| ... | ... | |
| 364 | 418 |
EditableFeatureType eFeatureType = featureType.getEditable(); |
| 365 | 419 |
eFeatureType.add("Compu1", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("ID*2")));
|
| 366 | 420 |
eFeatureType.add("Compu2", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+300")));
|
| 421 |
eFeatureType.add("Compu3", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("1")));
|
|
| 367 | 422 |
EditableFeatureAttributeDescriptor extraColumn1 = query.getExtraColumn().add("Extra1", DataTypes.INTEGER);
|
| 368 | 423 |
EditableFeatureAttributeDescriptor extraColumn2 = query.getExtraColumn().add("Extra2", DataTypes.INTEGER);
|
| 369 | 424 |
extraColumn1.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+10+Compu1")));
|
| ... | ... | |
| 376 | 431 |
query.getAggregateFunctions().put("Double", "SUM");
|
| 377 | 432 |
query.getAggregateFunctions().put("Extra2", "SUM");
|
| 378 | 433 |
query.getAggregateFunctions().put("Compu2", "SUM");
|
| 434 |
query.getAggregateFunctions().put("Compu3", "SUM");
|
|
| 379 | 435 |
ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
| 380 | 436 |
table, |
| 381 | 437 |
null, |
| ... | ... | |
| 398 | 454 |
} |
| 399 | 455 |
} |
| 400 | 456 |
|
| 457 |
public void testGroupByAndOrderByComputed() throws Exception {
|
|
| 458 |
try {
|
|
| 459 |
JDBCHelper helper = TestUtils.createJDBCHelper(); |
|
| 460 |
JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
|
| 461 |
OperationsFactory operations = helper.getOperations(); |
|
| 462 |
|
|
| 463 |
List<String> expectedSQLs = TestUtils.getSQLs("resultSetForSetProvider.sql");
|
|
| 464 |
|
|
| 465 |
FeatureStore sourceStore = TestUtils.openSourceStore1(); |
|
| 466 |
|
|
| 467 |
TableReference table = operations.createTableReference( |
|
| 468 |
"dbtest", |
|
| 469 |
sqlbuilder.default_schema(), |
|
| 470 |
"test", |
|
| 471 |
null |
|
| 472 |
); |
|
| 473 |
FeatureType featureType = sourceStore.getDefaultFeatureType(); |
|
| 474 |
FeatureQuery query = sourceStore.createFeatureQuery(); |
|
| 475 |
EditableFeatureType eFeatureType = featureType.getEditable(); |
|
| 476 |
eFeatureType.add("Compu1", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("ID*2")));
|
|
| 477 |
eFeatureType.add("Compu2", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+300")));
|
|
| 478 |
eFeatureType.add("Compu3", DataTypes.INTEGER, new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("1")));
|
|
| 479 |
|
|
| 480 |
EditableFeatureAttributeDescriptor extraColumn1 = query.getExtraColumn().add("Extra1", DataTypes.INTEGER);
|
|
| 481 |
extraColumn1.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+10+Compu1")));
|
|
| 482 |
|
|
| 483 |
EditableFeatureAttributeDescriptor extraColumn2 = query.getExtraColumn().add("Extra2", DataTypes.INTEGER);
|
|
| 484 |
extraColumn2.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("20+Byte+Compu1")));
|
|
| 485 |
|
|
| 486 |
EditableFeatureAttributeDescriptor extraColumn3 = query.getExtraColumn().add("Extra3", DataTypes.INTEGER);
|
|
| 487 |
extraColumn3.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression("Long+33")));
|
|
| 488 |
|
|
| 489 |
query.getGroupByColumns().add("Long");
|
|
| 490 |
query.getGroupByColumns().add("Extra1");
|
|
| 491 |
query.getGroupByColumns().add("Compu1");
|
|
| 492 |
query.getAggregateFunctions().put("ID", "MIN");
|
|
| 493 |
query.getAggregateFunctions().put("Byte", "MAX");
|
|
| 494 |
query.getAggregateFunctions().put("Double", "SUM");
|
|
| 495 |
query.getAggregateFunctions().put("Extra2", "SUM");
|
|
| 496 |
query.getAggregateFunctions().put("Compu2", "SUM");
|
|
| 497 |
query.getAggregateFunctions().put("Compu3", "SUM");
|
|
| 498 |
query.getOrder().add("Extra1");
|
|
| 499 |
query.getOrder().add("Extra2");
|
|
| 500 |
query.getOrder().add("Extra3");
|
|
| 501 |
ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
|
| 502 |
table, |
|
| 503 |
null, |
|
| 504 |
null, |
|
| 505 |
query, |
|
| 506 |
eFeatureType, |
|
| 507 |
eFeatureType, |
|
| 508 |
0, |
|
| 509 |
0, |
|
| 510 |
0 |
|
| 511 |
); |
|
| 512 |
String sql = resultSetForSetProvider.getSQL(); |
|
| 513 |
System.out.println("###### SQL:" + sql);
|
|
| 514 |
System.out.println("###### EXP:" + expectedSQLs.get(11));
|
|
| 515 |
|
|
| 516 |
assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get(11), sql);
|
|
| 517 |
} catch (Throwable th) {
|
|
| 518 |
LOGGER.warn("", th);
|
|
| 519 |
throw th; |
|
| 520 |
} |
|
| 521 |
} |
|
| 522 |
|
|
| 401 | 523 |
public void testSubselect() throws Exception {
|
| 402 | 524 |
JDBCHelper helper = TestUtils.createJDBCHelper(); |
| 403 | 525 |
JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
Also available in: Unified diff