Revision 47735
| trunk/org.gvsig.desktop/org.gvsig.desktop.compat.cdc/org.gvsig.fmap.dal/org.gvsig.fmap.dal.impl/src/test/java/org/gvsig/expressionevaluator/TestGrammarCompiler.java | ||
|---|---|---|
| 7 | 7 |
import org.gvsig.fmap.dal.feature.spi.SQLBuilderBase; |
| 8 | 8 |
import org.gvsig.json.Json; |
| 9 | 9 |
import org.gvsig.tools.library.impl.DefaultLibrariesInitializer; |
| 10 |
import org.slf4j.Logger; |
|
| 11 |
import org.slf4j.LoggerFactory; |
|
| 10 | 12 |
|
| 11 | 13 |
/** |
| 12 | 14 |
* |
| ... | ... | |
| 14 | 16 |
*/ |
| 15 | 17 |
public class TestGrammarCompiler extends TestCase {
|
| 16 | 18 |
|
| 19 |
private static Logger LOGGER = LoggerFactory.getLogger(TestGrammarCompiler.class); |
|
| 20 |
|
|
| 17 | 21 |
public TestGrammarCompiler(String testName) {
|
| 18 | 22 |
super(testName); |
| 19 | 23 |
} |
| ... | ... | |
| 229 | 233 |
|
| 230 | 234 |
Code code = compiler.compileExpression(source.toString()); |
| 231 | 235 |
link(code); |
| 232 |
checkEquals("testSelect2", "BEGIN \"X\" := 0; FOR \"row\" IN ( SELECT * FROM \"countries\" ) LOOP IF (\"row\".\"LASTCENSUS\" > 0) THEN \"X\" := (\"X\" + \"row\".\"LASTCENSUS\") END IF END LOOP; END ", code.toString());
|
|
| 236 |
checkEquals("testSelect2", "BEGIN \"X\" := 0; FOR \"row\" IN ( SELECT * FROM \"countries\" ) LOOP IF (\"row\".\"LASTCENSUS\" > 0) THEN \"X\" := (\"X\" + \"row\".\"LASTCENSUS\"); END IF END LOOP; END ", code.toString());
|
|
| 233 | 237 |
} |
| 234 | 238 |
|
| 235 | 239 |
public void testSelect3() {
|
| ... | ... | |
| 248 | 252 |
|
| 249 | 253 |
Code code = compiler.compileExpression(source.toString()); |
| 250 | 254 |
link(code); |
| 251 |
checkEquals("testSelect3", "BEGIN \"X\" := 0; FOR \"row\" IN ( SELECT * FROM \"countries\" ) LOOP IF (\"row\".\"LASTCENSUS\" > 0) THEN \"X\" := (\"X\" + \"row\".\"LASTCENSUS\") END IF END LOOP; END ", code.toString());
|
|
| 255 |
checkEquals("testSelect3", "BEGIN \"X\" := 0; FOR \"row\" IN ( SELECT * FROM \"countries\" ) LOOP IF (\"row\".\"LASTCENSUS\" > 0) THEN \"X\" := (\"X\" + \"row\".\"LASTCENSUS\"); END IF END LOOP; END ", code.toString());
|
|
| 252 | 256 |
} |
| 253 | 257 |
|
| 254 | 258 |
public void testSelect4() {
|
| ... | ... | |
| 267 | 271 |
|
| 268 | 272 |
Code code = compiler.compileExpression(source.toString()); |
| 269 | 273 |
link(code); |
| 270 |
checkEquals("testSelect4", "BEGIN \"X\" := 0; FOR \"row\" IN ( SELECT * FROM \"countries\" ) LOOP IF (\"row\".\"LASTCENSUS\" > 0) THEN \"X\" := (\"X\" + \"row\".\"LASTCENSUS\") END IF END LOOP; END ", code.toString());
|
|
| 274 |
checkEquals("testSelect4", "BEGIN \"X\" := 0; FOR \"row\" IN ( SELECT * FROM \"countries\" ) LOOP IF (\"row\".\"LASTCENSUS\" > 0) THEN \"X\" := (\"X\" + \"row\".\"LASTCENSUS\"); END IF END LOOP; END ", code.toString());
|
|
| 271 | 275 |
} |
| 272 | 276 |
|
| 273 | 277 |
public void testSelect5() {
|
| ... | ... | |
| 340 | 344 |
StringBuilder source = new StringBuilder(); |
| 341 | 345 |
source.append("BEGIN");
|
| 342 | 346 |
source.append(" tableName := 'countries';");
|
| 343 |
source.append(" SELECT COUNT(*) FROM :tableName ");
|
|
| 347 |
source.append(" SELECT COUNT(*) FROM :(tableName) ");
|
|
| 344 | 348 |
source.append(" WHERE countries.LASTCENSUS > 0 ; ");
|
| 345 | 349 |
source.append("END");
|
| 346 | 350 |
|
| ... | ... | |
| 348 | 352 |
|
| 349 | 353 |
Code code = compiler.compileExpression(source.toString()); |
| 350 | 354 |
link(code); |
| 351 |
checkEquals("testSelectCountWhere2", "BEGIN \"tableName\" := 'countries'; ( SELECT COUNT(*) FROM :IN (\"tableName\") WHERE (\"countries\".\"LASTCENSUS\" > 0) ); END ", code.toString());
|
|
| 355 |
checkEquals("testSelectCountWhere2", "BEGIN \"tableName\" := 'countries'; ( SELECT COUNT(*) FROM :(\"tableName\") WHERE (\"countries\".\"LASTCENSUS\" > 0) ); END ", code.toString());
|
|
| 352 | 356 |
} |
| 353 | 357 |
|
| 354 | 358 |
public void testSelectWhere2() {
|
| ... | ... | |
| 375 | 379 |
|
| 376 | 380 |
Code code = compiler.compileExpression(source.toString()); |
| 377 | 381 |
link(code); |
| 378 |
checkEquals("testSelectWhere3", "BEGIN \"tableName\" := 'countries'; ( SELECT * FROM :IN (\"tableName\") WHERE (\"countries\".\"LASTCENSUS\" > 0) ); END ", code.toString());
|
|
| 382 |
checkEquals("testSelectWhere3", "BEGIN \"tableName\" := 'countries'; ( SELECT * FROM :(\"tableName\") WHERE (\"countries\".\"LASTCENSUS\" > 0) ); END ", code.toString());
|
|
| 379 | 383 |
} |
| 380 | 384 |
|
| 381 | 385 |
public void testSelectWhereToValue3() {
|
| ... | ... | |
| 437 | 441 |
checkEquals("testSelectWhereOrder", "( SELECT * FROM \"countries\" WHERE (\"LASTCENSUS\" > 0) ORDER BY \"ID\" ASC NULLS LAST )", code.toString());
|
| 438 | 442 |
} |
| 439 | 443 |
|
| 444 |
public void testSelectWhereOrderNullsLast() {
|
|
| 445 |
StringBuilder source = new StringBuilder(); |
|
| 446 |
source.append("SELECT * FROM countries ");
|
|
| 447 |
source.append(" WHERE LASTCENSUS > 0 ");
|
|
| 448 |
source.append(" ORDER BY ID NULLS LAST");
|
|
| 449 |
|
|
| 450 |
Compiler compiler = createCompiler(); |
|
| 451 |
|
|
| 452 |
Code code = compiler.compileExpression(source.toString()); |
|
| 453 |
link(code); |
|
| 454 |
checkEquals("testSelectWhereOrder", "( SELECT * FROM \"countries\" WHERE (\"LASTCENSUS\" > 0) ORDER BY \"ID\" ASC NULLS LAST )", code.toString());
|
|
| 455 |
} |
|
| 456 |
|
|
| 457 |
public void testSelectWhereOrderNullsFirst() {
|
|
| 458 |
StringBuilder source = new StringBuilder(); |
|
| 459 |
source.append("SELECT * FROM countries ");
|
|
| 460 |
source.append(" WHERE LASTCENSUS > 0 ");
|
|
| 461 |
source.append(" ORDER BY ID NULLS FIRST");
|
|
| 462 |
|
|
| 463 |
Compiler compiler = createCompiler(); |
|
| 464 |
|
|
| 465 |
Code code = compiler.compileExpression(source.toString()); |
|
| 466 |
link(code); |
|
| 467 |
checkEquals("testSelectWhereOrder", "( SELECT * FROM \"countries\" WHERE (\"LASTCENSUS\" > 0) ORDER BY \"ID\" ASC NULLS LAST )", code.toString());
|
|
| 468 |
} |
|
| 469 |
|
|
| 440 | 470 |
public void testSelectWhereOrderLimit() {
|
| 441 | 471 |
StringBuilder source = new StringBuilder(); |
| 442 | 472 |
source.append("SELECT * FROM countries ");
|
| ... | ... | |
| 451 | 481 |
checkEquals("testSelectWhereOrderLimit", "( SELECT * FROM \"countries\" WHERE (\"LASTCENSUS\" > 0) ORDER BY \"LASTCENSUS\" DESC NULLS LAST LIMIT 3 )", code.toString());
|
| 452 | 482 |
} |
| 453 | 483 |
|
| 484 |
public void testSelectWhereOrderNullsLastLimit() {
|
|
| 485 |
StringBuilder source = new StringBuilder(); |
|
| 486 |
source.append("SELECT * FROM countries ");
|
|
| 487 |
source.append(" WHERE LASTCENSUS > 0 ");
|
|
| 488 |
source.append(" ORDER BY LASTCENSUS DESC NULLS LAST");
|
|
| 489 |
source.append(" LIMIT 3");
|
|
| 490 |
|
|
| 491 |
Compiler compiler = createCompiler(); |
|
| 492 |
|
|
| 493 |
Code code = compiler.compileExpression(source.toString()); |
|
| 494 |
link(code); |
|
| 495 |
checkEquals("testSelectWhereOrderLimit", "( SELECT * FROM \"countries\" WHERE (\"LASTCENSUS\" > 0) ORDER BY \"LASTCENSUS\" DESC NULLS LAST LIMIT 3 )", code.toString());
|
|
| 496 |
} |
|
| 497 |
|
|
| 498 |
public void testSelectWhereOrderNullsFirstLimit() {
|
|
| 499 |
StringBuilder source = new StringBuilder(); |
|
| 500 |
source.append("SELECT * FROM countries ");
|
|
| 501 |
source.append(" WHERE LASTCENSUS > 0 ");
|
|
| 502 |
source.append(" ORDER BY LASTCENSUS DESC NULLS FIRST");
|
|
| 503 |
source.append(" LIMIT 3");
|
|
| 504 |
|
|
| 505 |
Compiler compiler = createCompiler(); |
|
| 506 |
|
|
| 507 |
Code code = compiler.compileExpression(source.toString()); |
|
| 508 |
link(code); |
|
| 509 |
checkEquals("testSelectWhereOrderLimit", "( SELECT * FROM \"countries\" WHERE (\"LASTCENSUS\" > 0) ORDER BY \"LASTCENSUS\" DESC NULLS LAST LIMIT 3 )", code.toString());
|
|
| 510 |
} |
|
| 511 |
|
|
| 454 | 512 |
public void testSelectWhereOrderLimit2() {
|
| 455 | 513 |
try {
|
| 456 | 514 |
StringBuilder source = new StringBuilder(); |
| ... | ... | |
| 648 | 706 |
checkEquals("testSelectFromSelection4-1", "BLOCK(LET('X', 0), FOREACH('row', SELECT(TUPLE(), \"countries\", NULL, TUPLE(), TUPLE(), NULL, 1), IF((GETATTR(\"row\", 'LASTCENSUS') > 0), LET('X', (\"X\" + GETATTR(\"row\", 'LASTCENSUS'))))))", code.toString());
|
| 649 | 707 |
link(code); |
| 650 | 708 |
String source2 = code.toString(); |
| 651 |
checkEquals("testSelectFromSelection4-2", "BEGIN \"X\" := 0; FOR \"row\" IN ( SELECT * FROM SELECTION OF \"countries\" ) LOOP IF (\"row\".\"LASTCENSUS\" > 0) THEN \"X\" := (\"X\" + \"row\".\"LASTCENSUS\") END IF END LOOP; END ", source2);
|
|
| 709 |
checkEquals("testSelectFromSelection4-2", "BEGIN \"X\" := 0; FOR \"row\" IN ( SELECT * FROM SELECTION OF \"countries\" ) LOOP IF (\"row\".\"LASTCENSUS\" > 0) THEN \"X\" := (\"X\" + \"row\".\"LASTCENSUS\"); END IF END LOOP; END ", source2);
|
|
| 652 | 710 |
Code code2 = compiler.compileExpression(source2); |
| 653 | 711 |
checkEquals("testSelectFromSelection4-3", "BLOCK(LET('X', 0), FOREACH('row', SELECT(TUPLE(), \"countries\", NULL, TUPLE(), TUPLE(), NULL, 1), IF((GETATTR(\"row\", 'LASTCENSUS') > 0), LET('X', (\"X\" + GETATTR(\"row\", 'LASTCENSUS'))))))", code2.toString());
|
| 654 | 712 |
link(code2); |
| 655 |
checkEquals("testSelectFromSelection4-4", "BEGIN \"X\" := 0; FOR \"row\" IN ( SELECT * FROM SELECTION OF \"countries\" ) LOOP IF (\"row\".\"LASTCENSUS\" > 0) THEN \"X\" := (\"X\" + \"row\".\"LASTCENSUS\") END IF END LOOP; END ", code2.toString());
|
|
| 713 |
checkEquals("testSelectFromSelection4-4", "BEGIN \"X\" := 0; FOR \"row\" IN ( SELECT * FROM SELECTION OF \"countries\" ) LOOP IF (\"row\".\"LASTCENSUS\" > 0) THEN \"X\" := (\"X\" + \"row\".\"LASTCENSUS\"); END IF END LOOP; END ", code2.toString());
|
|
| 656 | 714 |
|
| 657 | 715 |
} |
| 658 | 716 |
|
| 717 |
public void testUseCase1() {
|
|
| 718 |
try {
|
|
| 719 |
String source = |
|
| 720 |
"BEGIN\n" + |
|
| 721 |
" IF urbanroads IS NULL THEN\n" + |
|
| 722 |
" calle := NULL;\n" + |
|
| 723 |
" urbanroadtype := NULL;\n" + |
|
| 724 |
" \n" + |
|
| 725 |
" IF thegeometry IS NULL THEN\n" + |
|
| 726 |
" RETURN TRUE;\n" + |
|
| 727 |
" END IF;\n" + |
|
| 728 |
" SELECT * INTO calle FROM \"ISIROAD_URBANROADS\" \n" + |
|
| 729 |
" ORDER BY ST_DISTANCE(\"ISIROAD_URBANROADS\".\"Geometria\", thegeometry) \n" + |
|
| 730 |
" LIMIT 1;\n" + |
|
| 731 |
" IF calle IS NULL THEN\n" + |
|
| 732 |
" RETURN TRUE;\n" + |
|
| 733 |
" END IF;\n" + |
|
| 734 |
" urbanroads := calle.Id;\n" + |
|
| 735 |
" \n" + |
|
| 736 |
" SELECT * INTO tipodecalle FROM \"ISIROAD_URBANROADTYPES\" \n" + |
|
| 737 |
" WHERE \"ISIROAD_URBANROADTYPES\".\"DESCRIPTION\" = :(calle.Descripcion)\n" + |
|
| 738 |
" LIMIT 1; \n" + |
|
| 739 |
" urbanroadtype := tipodecalle.CODE;\n" + |
|
| 740 |
" END IF;\n" + |
|
| 741 |
" TRUE;\n" + |
|
| 742 |
"EXCEPT\n" + |
|
| 743 |
" print('Error en el calculo de calle de horizontal');\n" +
|
|
| 744 |
" TRUE;\n" + |
|
| 745 |
"END"; |
|
| 746 |
|
|
| 747 |
Formatter formatter = new Code.EmptyFormatter(true, 4); |
|
| 748 |
|
|
| 749 |
Compiler compiler = createCompiler(); |
|
| 750 |
Code code = compiler.compileExpression(source); |
|
| 751 |
code.link(); |
|
| 752 |
checkEquals( |
|
| 753 |
"BEGIN \n" + |
|
| 754 |
" IF (\"urbanroads\" IS NULL) THEN \n" + |
|
| 755 |
" \"calle\" := NULL; \n" + |
|
| 756 |
" \"urbanroadtype\" := NULL; \n" + |
|
| 757 |
" IF (\"thegeometry\" IS NULL) THEN \n" + |
|
| 758 |
" RETURN TRUE; \n" + |
|
| 759 |
" END IF; \n" + |
|
| 760 |
" ( SELECT * INTO calle FROM \"ISIROAD_URBANROADS\" ORDER BY ST_Distance(\"ISIROAD_URBANROADS\".\"Geometria\", \"thegeometry\") ASC NULLS LAST LIMIT 1 ); \n" + |
|
| 761 |
" IF (\"calle\" IS NULL) THEN \n" + |
|
| 762 |
" RETURN TRUE; \n" + |
|
| 763 |
" END IF; \n" + |
|
| 764 |
" \"urbanroads\" := \"calle\".\"Id\"; \n" + |
|
| 765 |
" ( SELECT * INTO tipodecalle FROM \"ISIROAD_URBANROADTYPES\" WHERE (\"ISIROAD_URBANROADTYPES\".\"DESCRIPTION\" = $HOSTEXPRESSION(\"calle\".\"Descripcion\")) LIMIT 1 ); \n" + |
|
| 766 |
" \"urbanroadtype\" := \"tipodecalle\".\"CODE\"; \n" + |
|
| 767 |
" END IF; \n" + |
|
| 768 |
" TRUE; \n" + |
|
| 769 |
"EXCEPT \n" + |
|
| 770 |
" print('Error en el calculo de calle de horizontal'); \n" +
|
|
| 771 |
" TRUE; \n" + |
|
| 772 |
"END \n", |
|
| 773 |
code.toString(formatter)); |
|
| 774 |
} catch(Exception ex) {
|
|
| 775 |
LOGGER.warn("", ex);
|
|
| 776 |
throw ex; |
|
| 777 |
} |
|
| 778 |
} |
|
| 779 |
|
|
| 780 |
|
|
| 781 |
|
|
| 659 | 782 |
} |
Also available in: Unified diff