Examples of StructuredSQL

eu.balticdiversity.search.models.StructuredSQL

Examples of eu.balticdiversity.search.models.StructuredSQL

  @Override
  public List<AggregateResponseEntry> getAggregatedBySource(Query query) throws SQLException {
    AggregatedQuery.SQLModifier modifier = new AggregatedQuery.SQLModifier() {
      @Override
      public String modify(String originalSQL) {
        StructuredSQL sql = new StructuredSQL(originalSQL);
        sql.setSelect(sql.getSelect().replace("Latitude05,Longitude", "Title.SourceName"));
        sql.setFrom(sql.getFrom() + " JOIN Mustikka.OLAP.Source AS Title ON Title.SourceID = SourceFK ");
        sql.setGroupBy("Title.SourceName");
        sql.setOrderBy("Lkm DESC");
        return sql.toString();
      }
    };
    return aggregatedQueriesCache.get(new AggregatedQuery(query, modifier, "source"));
  }

View Full Code Here

Examples of eu.balticdiversity.search.models.StructuredSQL

  @Override
  public List<AggregateResponseEntry> getAggregatedByProject(Query query) throws SQLException {
    AggregatedQuery.SQLModifier modifier = new AggregatedQuery.SQLModifier() {
      @Override
      public String modify(String originalSQL) {
        StructuredSQL sql = new StructuredSQL(originalSQL);
        sql.setSelect(sql.getSelect().replace("Latitude05,Longitude", "Title.ProjectName"));
        sql.setFrom(sql.getFrom() + " JOIN Mustikka.OLAP.Project AS Title ON Title.ProjectID = ProjectFK ");
        sql.setGroupBy("Title.ProjectName");
        sql.setOrderBy("Lkm DESC");
        return sql.toString();
      }
    };
    return aggregatedQueriesCache.get(new AggregatedQuery(query, modifier, "project"));
  }

View Full Code Here

Examples of eu.balticdiversity.search.models.StructuredSQL

  @Override
  public List<AggregateResponseEntry> getAggregatedByCollection(Query query) throws SQLException {
    AggregatedQuery.SQLModifier modifier = new AggregatedQuery.SQLModifier() {
      @Override
      public String modify(String originalSQL) {
        StructuredSQL sql = new StructuredSQL(originalSQL);
        sql.setSelect(sql.getSelect().replace("Latitude05,Longitude", "CASE WHEN TitleProject.CollectionURI IS NULL THEN TitleSource.CollectionURI ELSE TitleProject.CollectionURI END AS Collection"));
        sql.setFrom(sql.getFrom() + " JOIN Mustikka.OLAP.Project AS TitleProject ON TitleProject.ProjectID = ProjectFK  JOIN Mustikka.OLAP.Source AS TitleSource ON TitleSource.SourceID = SourceFK ");
        sql.setGroupBy("CASE WHEN TitleProject.CollectionURI IS NULL THEN TitleSource.CollectionURI ELSE TitleProject.CollectionURI END");
        sql.setOrderBy("Lkm DESC");
        return sql.toString();
      }
    };
    return aggregatedQueriesCache.get(new AggregatedQuery(query, modifier, "collection"));
  }

View Full Code Here

Examples of eu.balticdiversity.search.models.StructuredSQL

  @Override
  public List<AggregateResponseEntry> getAggregatedByCountry(Query query) throws SQLException {
    AggregatedQuery.SQLModifier modifier = new AggregatedQuery.SQLModifier() {
      @Override
      public String modify(String originalSQL) {
        StructuredSQL sql = new StructuredSQL(originalSQL);
        sql.setSelect(sql.getSelect().replace("Latitude05,Longitude", "Title.NameFinnish"));
        sql.setFrom(sql.getFrom() + " JOIN Mustikka.Area.Country AS Title ON Title.CountryID = CountryFK ");
        sql.setGroupBy("Title.NameFinnish");
        sql.setOrderBy("Lkm DESC");
        return sql.toString();
      }
    };
    return aggregatedQueriesCache.get(new AggregatedQuery(query, modifier, "country"));
  }

View Full Code Here

Examples of eu.balticdiversity.search.models.StructuredSQL

  @Override
  public List<AggregateResponseEntry> getAggregatedByClass(Query query) throws SQLException {
    AggregatedQuery.SQLModifier modifier = new AggregatedQuery.SQLModifier() {
      @Override
      public String modify(String originalSQL) {
        StructuredSQL sql = new StructuredSQL(originalSQL);
        sql.setSelect(sql.getSelect().replace("Latitude05,Longitude", "Title.FullName, Title.FinnishName"));
        sql.setFrom(sql.getFrom() + " JOIN Mustikka.OLAP.Taxon AS Title ON Title.TaxonID = Unit.ClassFK ");
        sql.setGroupBy("Title.FullName, Title.FinnishName");
        sql.setOrderBy("Lkm DESC");
        return sql.toString();
      }
    };
    return aggregatedQueriesCache.get(new AggregatedQuery(query, modifier, "scientificName", "vernacularName"));
  }

View Full Code Here

Examples of eu.balticdiversity.search.models.StructuredSQL

  @Override
  public List<AggregateResponseEntry> getAggregatedByDecade(Query query) throws SQLException {
    AggregatedQuery.SQLModifier modifier = new AggregatedQuery.SQLModifier() {
      @Override
      public String modify(String originalSQL) {
        StructuredSQL sql = new StructuredSQL(originalSQL);
        sql.setSelect(sql.getSelect().replace("Latitude05,Longitude", "Decade"));
        sql.setGroupBy("Decade");
        sql.setOrderBy("Decade DESC ");
        return sql.toString();
      }
    };
    return aggregatedQueriesCache.get(new AggregatedQuery(query, modifier, "decade"));
  }

View Full Code Here

Examples of eu.balticdiversity.search.models.StructuredSQL

  @Override
  public List<AggregateResponseEntry> getAggregatedByRecordBasis(Query query) throws SQLException {
    AggregatedQuery.SQLModifier modifier = new AggregatedQuery.SQLModifier() {
      @Override
      public String modify(String originalSQL) {
        StructuredSQL sql = new StructuredSQL(originalSQL);
        sql.setSelect(sql.getSelect().replace("Latitude05,Longitude", "RecordBasis.Description"));
        sql.setFrom(sql.getFrom() + " JOIN Mustikka.OLAP.RecordBasis ON RecordBasis.RecordBasisID = Unit.RecordBasisFK ");
        sql.setGroupBy("RecordBasis.Description");
        sql.setOrderBy("Lkm DESC ");
        return sql.toString();
      }
    };
    return aggregatedQueriesCache.get(new AggregatedQuery(query, modifier, "recordBasis"));
  }

View Full Code Here

Examples of eu.balticdiversity.search.models.StructuredSQL


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());
  }

View Full Code Here

Examples of eu.balticdiversity.search.models.StructuredSQL

    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());
  }

View Full Code Here

TOP

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.