Package eu.balticdiversity.search.models

Examples of eu.balticdiversity.search.models.StructuredSQL

318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
  @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
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
  @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
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
  @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
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
  @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
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
  @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
398
399
400
401
402
403
404
405
406
407
408
409
410
411
  @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
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
  @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
8
9
10
11
12
13
14
15
16
17
18
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
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
    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

Related Classes of eu.balticdiversity.search.models.StructuredSQL

  • eu.balticdiversity.search.dao.DAOImple
  • eu.balticdiversity.search.StructuredSQLTests
    • Copyright © 2018 www.massapicom. All rights reserved.
    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.