src/main/java/eu/etaxonomy/cdm/vaadin/util/CdmQueryFactory.java

   1 // $Id$
   2 /**
   3 * Copyright (C) 2015 EDIT
   4 * European Distributed Institute of Taxonomy
   5 * http://www.e-taxonomy.eu
   6 *
   7 * The contents of this file are subject to the Mozilla Public License Version 1.1
   8 * See LICENSE.TXT at the top of this package for the full license terms.
   9 */
  10 package eu.etaxonomy.cdm.vaadin.util;
  11
  12 import java.sql.SQLException;
  13 import java.util.List;
  14
  15 import com.vaadin.data.util.sqlcontainer.query.FreeformQuery;
  16 import com.vaadin.data.util.sqlcontainer.query.QueryDelegate;
  17
  18 import eu.etaxonomy.cdm.vaadin.statement.CdmStatementDelegate;
  19
  20 /**
  21  * @author cmathew
  22  * @date 1 Apr 2015
  23  *
  24  */
  25 public class CdmQueryFactory {
  26
  27     public static final String ID = "id";
  28     public static final String UUID_ID = "uuid";
  29
  30     public static QueryDelegate generateTaxonBaseQuery(String name_id,
  31             String pb_id,
  32             String unp_id,
  33             String rank_id,
  34             String has_syn_id) throws SQLException {
  35         String FROM_QUERY = " FROM TaxonBase tb " +
  36                 "INNER JOIN TaxonNode tn on tn.taxon_id=tb.id " +
  37                 "INNER JOIN TaxonNameBase tnb on tb.name_id=tnb.id " +
  38                 "INNER JOIN DefinedTermBase dtb on tnb.rank_id=dtb.id  ";
  39         String SELECT_QUERY="SELECT tb.id as " + ID +
  40                 ", tb.uuid as " + UUID_ID +
  41                 ", tnb.titleCache as " + name_id +
  42                 ", tb.publish as " + pb_id +
  43                 ", tb.unplaced as " + unp_id +
  44                 ", dtb.titleCache as " + rank_id +
  45                 ", (SELECT COUNT(*) FROM  SynonymRelationship sr WHERE tb.id = sr.relatedto_id) as " + has_syn_id +
  46                 FROM_QUERY;
  47         String COUNT_QUERY = "SELECT count(*) " + FROM_QUERY;
  48         String CONTAINS_QUERY = "SELECT * FROM TaxonBase tb WHERE tb.id = ?";
  49
  50         return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
  51     }
  52
  53     public static QueryDelegate generateTaxonDistributionQuery(List<String> termList, int classificationID) throws SQLException {
  54         String FROM_QUERY =
  55                         " FROM TaxonNode tn " +
  56                         "INNER JOIN TaxonBase tb on tn.taxon_id = tb.id " +
  57                         "INNER JOIN Classification cl ON tn.classification_id = cl.id " +
  58                         "LEFT OUTER JOIN TaxonNameBase tnb ON tnb.id=tb.id " +
  59                         "LEFT OUTER JOIN DescriptionBase db ON db.taxon_id=tb.id " +
  60                         "LEFT OUTER JOIN (SELECT indescription_id, area_id, status_id, DTYPE, id FROM DescriptionElementBase deb WHERE deb.DTYPE LIKE 'Distribution') AS deb ON deb.indescription_id=db.id " +
  61                         "LEFT OUTER JOIN DefinedTermBase dtb on deb.status_id=dtb.id " +
  62                         "LEFT OUTER JOIN DefinedTermBase dtb1 on deb.area_id=dtb1.id " +
  63                         "LEFT OUTER JOIN DefinedTermBase dtb2 on tnb.rank_id = dtb2.id " +
  64                         "WHERE tn.classification_id = "+ classificationID +" AND tb.DTYPE = 'Taxon'" ;
  65
  66         String GROUP_BY = " GROUP BY tb.id ";
  67
  68         String SELECT_QUERY=
  69                         "SELECT tb.DTYPE," +
  70                         "tb.id, " +
  71                         "tn.classification_id, " +
  72                         "tb.titleCache AS Taxon, " +
  73                         "dtb2.titleCache AS Rank, " +
  74                         "deb.DTYPE," +
  75                         "deb.id, " +
  76                         "deb.area_id, "+
  77                         "dtb.vocabulary_id, " +
  78                         "dtb1.vocabulary_id, ";
  79         int count = termList.size();
  80         for(String term : termList){
  81                 if(count == 1){
  82                         SELECT_QUERY= SELECT_QUERY +
  83                                 "MAX( IF(dtb1.titleCache = '"+ term +"', dtb.titleCache, NULL) ) as '"+ term +"' " ;
  84                 }else{
  85                         SELECT_QUERY= SELECT_QUERY +
  86                                         "MAX( IF(dtb1.titleCache = '"+ term +"', dtb.titleCache, NULL) ) as '"+ term +"'," ;
  87                 }
  88                 count--;
  89         }
  90         SELECT_QUERY= SELECT_QUERY + FROM_QUERY + GROUP_BY;
  91         String COUNT_QUERY = "SELECT count(DISTINCT tb.id)" + FROM_QUERY;
  92         String CONTAINS_QUERY = "SELECT * FROM TaxonNode tn WHERE tn.id = ?";
  93
  94         return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
  95     }
  96
  97     public static QueryDelegate generateSynonymofTaxonQuery(String name_id) throws SQLException {
  98         String FROM_QUERY = " FROM TaxonBase tb " +
  99                         "INNER JOIN TaxonNameBase tnb on tb.name_id=tnb.id " +
 100                         "INNER JOIN SynonymRelationship sr on tb.id=sr.relatedfrom_id ";
 101         String SELECT_QUERY="SELECT tb.id as " + ID +
 102                         ", tnb.titleCache as " + name_id +
 103                         FROM_QUERY;
 104         String COUNT_QUERY = "SELECT count(*) " + FROM_QUERY;
 105         String CONTAINS_QUERY = "SELECT * FROM SynonymRelationship sr WHERE sr.relatedfrom_id = ?";
 106
 107         return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
 108     }
 109
 110     /**
 111      * Creates a FreeformQuery which mimics a TableQuery.
 112      * This method works around the bug at http://dev.vaadin.com/ticket/12370
 113      *
 114      * @param tableName
 115      * @return
 116      * @throws SQLException
 117      */
 118     public static QueryDelegate generateTableQuery(String tableName) throws SQLException {
 119         String FROM_QUERY = " FROM " + tableName;
 120         String SELECT_QUERY=" SELECT * " +
 121                 FROM_QUERY;
 122         String COUNT_QUERY = "SELECT count(*) " + FROM_QUERY;
 123         String CONTAINS_QUERY = "SELECT * FROM " + tableName + "  WHERE id = ?";
 124
 125         return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
 126     }
 127
 128     public static QueryDelegate generateQueryDelegate(String SELECT_QUERY, String COUNT_QUERY, String CONTAINS_QUERY) throws SQLException {
 129         FreeformQuery query = new FreeformQuery("This query is not used", CdmSpringContextHelper.getCurrent().getConnectionPool(), ID);
 130         CdmStatementDelegate delegate = new CdmStatementDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
 131         query.setDelegate(delegate);
 132         return query;
 133     }
 134
 135 }