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.Collection;
  14 import java.util.Iterator;
  15 import java.util.List;
  16 import java.util.regex.Matcher;
  17 import java.util.regex.Pattern;
  18
  19 import org.apache.commons.lang.StringUtils;
  20
  21 import com.vaadin.data.util.sqlcontainer.query.FreeformQuery;
  22 import com.vaadin.data.util.sqlcontainer.query.QueryDelegate;
  23
  24 import eu.etaxonomy.cdm.model.common.Language;
  25 import eu.etaxonomy.cdm.model.common.Representation;
  26 import eu.etaxonomy.cdm.model.location.NamedArea;
  27 import eu.etaxonomy.cdm.vaadin.statement.CdmStatementDelegate;
  28
  29 /**
  30  * @author cmathew
  31  * @date 1 Apr 2015
  32  *
  33  */
  34 public class CdmQueryFactory {
  35
  36
  37         public static final String RANK_COLUMN = "Rank";
  38         public static final String TAXON_COLUMN = "Taxon";
  39
  40     public static final String ID = "id";
  41     public static final String UUID_ID = "uuid";
  42
  43     public static QueryDelegate generateTaxonBaseQuery(String name_id,
  44             String pb_id,
  45             String unp_id,
  46             String rank_id,
  47             String has_syn_id) throws SQLException {
  48         String FROM_QUERY = " FROM TaxonBase tb " +
  49                 "INNER JOIN TaxonNode tn on tn.taxon_id=tb.id " +
  50                 "INNER JOIN TaxonNameBase tnb on tb.name_id=tnb.id " +
  51                 "INNER JOIN DefinedTermBase dtb on tnb.rank_id = dtb.id";
  52         String SELECT_QUERY="SELECT tb.id as " + ID +
  53                 ", tb.uuid as " + UUID_ID +
  54                 ", tnb.titleCache as " + name_id +
  55                 ", tb.publish as " + pb_id +
  56                 ", tb.unplaced as " + unp_id +
  57                 ", dtb.titleCache as " + rank_id +
  58                 ", (SELECT COUNT(*) FROM  SynonymRelationship sr WHERE tb.id = sr.relatedto_id) as " + has_syn_id +
  59                 FROM_QUERY;
  60         String COUNT_QUERY = "SELECT count(*) " + FROM_QUERY;
  61         String CONTAINS_QUERY = "SELECT * FROM TaxonBase tb WHERE tb.id = ?";
  62
  63         return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
  64     }
  65
  66     public static QueryDelegate generateTaxonDistributionQuery(List<Integer> taxonNodeIds, Collection<NamedArea> namedAreas) throws SQLException {
  67
  68         String idString = "";
  69         Iterator<Integer> nodeIterator = taxonNodeIds.iterator();
  70         while (nodeIterator.hasNext()) {
  71                         Integer integer = nodeIterator.next();
  72                         idString += String.valueOf(integer);
  73                         if(nodeIterator.hasNext()){
  74                                 idString += ", ";
  75                         }
  76                 }
  77         String FROM_QUERY =
  78                 "FROM TaxonNameBase tnb "
  79                 + "INNER JOIN TaxonBase tb on tnb.id = tb.name_id and tb.DTYPE='Taxon' " + // # name<->taxon
  80         "INNER JOIN TaxonNode tn on tn.taxon_id = tb.id "+
  81         "INNER JOIN DefinedTermBase rank on tnb.rank_id = rank.id "+// # rank <-> name
  82         "LEFT OUTER JOIN DescriptionBase descr on descr.taxon_id = tb.id "+// # taxon <-> taxon description (not every taxon has a description)
  83         "LEFT OUTER JOIN DescriptionElementBase descrEl on descrEl.indescription_id = descr.id and descrEl.DTYPE = 'Distribution' "+// # distribution <-> description
  84         "LEFT OUTER JOIN DefinedTermBase statusTerm on statusTerm.id = descrEl.status_id "+
  85         "LEFT OUTER JOIN DefinedTermBase area on area.id = descrEl.area_id "+
  86         "WHERE tn.id IN ("+ idString +") ";
  87
  88         String GROUP_BY = " GROUP BY tb.uuid, tn.id ";
  89
  90         String ORDER_BY = " ORDER BY tb.titleCache ";
  91
  92         String SELECT_QUERY= "SELECT "
  93                 + "tb.DTYPE, "
  94                 + "tb.id, "
  95                 + "tb.uuid, "
  96                 + "tn.classification_id, "+
  97                         "tb.titleCache AS "+TAXON_COLUMN+", " +
  98                         "rank.titleCache AS "+RANK_COLUMN+", ";
  99
 100         for(NamedArea namedArea : namedAreas){
 101             String label = null;
 102             Representation representation = namedArea.getRepresentation(Language.DEFAULT());
 103             if(representation!=null){
 104                                 if(DistributionEditorUtil.isAbbreviatedLabels()){
 105                         label = representation.getAbbreviatedLabel();
 106                 }
 107                 else{
 108                         label = representation.getLabel();
 109                 }
 110             }
 111             if(label==null){
 112                 label = namedArea.getTitleCache();
 113             }
 114             SELECT_QUERY += "MAX( IF(area.titleCache = '"+ namedArea.getTitleCache() +"', statusTerm.titleCache, NULL) ) as '"+ label +"'," ;
 115         }
 116         SELECT_QUERY = StringUtils.stripEnd(SELECT_QUERY, ",")+" ";
 117         SELECT_QUERY= SELECT_QUERY + FROM_QUERY + GROUP_BY + ORDER_BY;
 118         String COUNT_QUERY = "SELECT count(DISTINCT tb.id)" + FROM_QUERY;
 119         String CONTAINS_QUERY = "SELECT * FROM TaxonBase tb WHERE tb.uuid = ?";
 120         //Escape SQL control character '
 121         Pattern p = Pattern.compile("(\\w+)'(\\w+)");
 122         Matcher m = p.matcher(SELECT_QUERY);
 123         if (m.find()) {
 124             SELECT_QUERY = m.replaceAll("$1\\\\'$2");
 125         }
 126         return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
 127     }
 128
 129     public static QueryDelegate generateSynonymofTaxonQuery(String name_id) throws SQLException {
 130         String FROM_QUERY = " FROM TaxonBase tb " +
 131                         "INNER JOIN TaxonNameBase tnb on tb.name_id=tnb.id " +
 132                         "INNER JOIN SynonymRelationship sr on tb.id=sr.relatedfrom_id ";
 133         String SELECT_QUERY="SELECT tb.id as " + ID +
 134                         ", tnb.titleCache as " + name_id +
 135                         FROM_QUERY;
 136         String COUNT_QUERY = "SELECT count(*) " + FROM_QUERY;
 137         String CONTAINS_QUERY = "SELECT * FROM SynonymRelationship sr WHERE sr.relatedfrom_id = ?";
 138
 139         return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
 140     }
 141
 142     public static QueryDelegate generateTaxonRelatedToQuery(String reluuid_id,
 143             String reltype_id,
 144             String to_id,
 145             String touuid_id,
 146             String toname_id) throws SQLException {
 147         String FROM_QUERY = "     FROM TaxonRelationship tr " +
 148                 "INNER JOIN TaxonBase tb on tr.relatedto_id = tb.id " +
 149                 "INNER JOIN TaxonNode tn on tb.id = tn.taxon_id ";
 150         String SELECT_QUERY= "SELECT tr.id as " + ID +
 151                 ", tr.uuid as " + reluuid_id +
 152                 ", tr.type_id as " + reltype_id +
 153                 ", tr.relatedto_id as " + to_id +
 154                 ", tb.uuid as " + touuid_id +
 155                 ", tb.titleCache as " + toname_id +
 156                 FROM_QUERY;
 157         String COUNT_QUERY = "SELECT count(*) " + FROM_QUERY;
 158         String CONTAINS_QUERY = "SELECT * FROM TaxonRelationship tr where tr.relatedfrom_id = ?";
 159
 160         return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
 161     }
 162
 163     /**
 164      * Creates a FreeformQuery which mimics a TableQuery.
 165      * This method works around the bug at http://dev.vaadin.com/ticket/12370
 166      *
 167      * @param tableName
 168      * @return
 169      * @throws SQLException
 170      */
 171     public static QueryDelegate generateTableQuery(String tableName) throws SQLException {
 172         String FROM_QUERY = " FROM " + tableName;
 173         String SELECT_QUERY=" SELECT * " +
 174                 FROM_QUERY;
 175         String COUNT_QUERY = "SELECT count(*) " + FROM_QUERY;
 176         String CONTAINS_QUERY = "SELECT * FROM " + tableName + "  WHERE id = ?";
 177
 178         return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
 179     }
 180
 181     public static QueryDelegate generateQueryDelegate(String SELECT_QUERY, String COUNT_QUERY, String CONTAINS_QUERY) throws SQLException {
 182         FreeformQuery query = new FreeformQuery("This query is not used", CdmSpringContextHelper.getCurrent().getConnectionPool(), ID);
 183         CdmStatementDelegate delegate = new CdmStatementDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
 184         query.setDelegate(delegate);
 185         return query;
 186     }
 187
 188 }