package eu.balticdiversity.search;
import static org.junit.Assert.assertEquals;
import eu.balticdiversity.search.models.StructuredSQL;
import org.junit.Test;
public class StructuredSQLTests {
@Test
public void parsing() {
StructuredSQL sql = new StructuredSQL(" select 1 from dual ");
assertEquals("1", sql.getSelect());
assertEquals("dual", sql.getFrom());
assertEquals("", sql.getWhere());
assertEquals("SELECT 1 FROM dual", sql.toString());
}
@Test
public void parsing2() {
StructuredSQL sql = new StructuredSQL("" +
"SELECT " +
"Latitude05,Longitude," +
"COUNT_BIG(*) AS Lkm," +
"SUM(CAST(Unit.IndividualCount AS BIGINT)) AS SumN," +
"MAX(Unit.IndividualCount) AS MaxN," +
"MIN(Unit.DateBeginFK) AS MinDate," +
"MAX(Unit.DateEndFK) AS MaxDate" +
"FROM Mustikka.OLAP.Unit " +
"JOIN Mustikka.OLAP.Location ON LocationFK = LocationID " +
"JOIN Mustikka.OLAP.TargetTaxon ON Unit.TargetFK = TargetTaxon.TargetID " +
"JOIN Mustikka.OLAP.NamedAreaIndexed ON NamedAreaIndexed.GatheringFK = Unit.GatheringFK " +
"JOIN Mustikka.OLAP.Project ON ProjectFK = ProjectID " +
"WHERE (Unit.OrderFK IN (53695)) " +
"AND AreaName IN ('suomi') " +
"GROUP BY Latitude05,Longitude " +
"ORDER BY Latitude05,Longitude");
assertEquals(
"Latitude05,Longitude,COUNT_BIG(*) AS Lkm,SUM(CAST(Unit.IndividualCount AS BIGINT)) AS SumN,MAX(Unit.IndividualCount) AS MaxN,MIN(Unit.DateBeginFK) AS MinDate,MAX(Unit.DateEndFK) AS MaxDate",
sql.getSelect());
assertEquals("Mustikka.OLAP.Unit " +
"JOIN Mustikka.OLAP.Location ON LocationFK = LocationID " +
"JOIN Mustikka.OLAP.TargetTaxon ON Unit.TargetFK = TargetTaxon.TargetID " +
"JOIN Mustikka.OLAP.NamedAreaIndexed ON NamedAreaIndexed.GatheringFK = Unit.GatheringFK " +
"JOIN Mustikka.OLAP.Project ON ProjectFK = ProjectID",
sql.getFrom());
assertEquals("(Unit.OrderFK IN (53695)) AND AreaName IN ('suomi')", sql.getWhere());
assertEquals("Latitude05,Longitude", sql.getGroupBy());
assertEquals("Latitude05,Longitude", sql.getOrderBy());
assertEquals("" +
"SELECT " +
"Latitude05,Longitude," +
"COUNT_BIG(*) AS Lkm," +
"SUM(CAST(Unit.IndividualCount AS BIGINT)) AS SumN," +
"MAX(Unit.IndividualCount) AS MaxN," +
"MIN(Unit.DateBeginFK) AS MinDate," +
"MAX(Unit.DateEndFK) AS MaxDate " +
"FROM Mustikka.OLAP.Unit " +
"JOIN Mustikka.OLAP.Location ON LocationFK = LocationID " +
"JOIN Mustikka.OLAP.TargetTaxon ON Unit.TargetFK = TargetTaxon.TargetID " +
"JOIN Mustikka.OLAP.NamedAreaIndexed ON NamedAreaIndexed.GatheringFK = Unit.GatheringFK " +
"JOIN Mustikka.OLAP.Project ON ProjectFK = ProjectID " +
"WHERE (Unit.OrderFK IN (53695)) " +
"AND AreaName IN ('suomi') " +
"GROUP BY Latitude05,Longitude " +
"ORDER BY Latitude05,Longitude",
sql.toString());
}
}