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

   1 /**
   2 * Copyright (C) 2015 EDIT
   3 * European Distributed Institute of Taxonomy
   4 * http://www.e-taxonomy.eu
   5 *
   6 * The contents of this file are subject to the Mozilla Public License Version 1.1
   7 * See LICENSE.TXT at the top of this package for the full license terms.
   8 */
   9 package eu.etaxonomy.cdm.vaadin.util;
  10
  11 import java.util.Collection;
  12 import java.util.HashMap;
  13 import java.util.Iterator;
  14 import java.util.List;
  15 import java.util.Map;
  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.common.CdmUtils;
  25 import eu.etaxonomy.cdm.model.common.Language;
  26 import eu.etaxonomy.cdm.model.common.Representation;
  27 import eu.etaxonomy.cdm.model.location.NamedArea;
  28 import eu.etaxonomy.cdm.vaadin.statement.CdmStatementDelegate;
  29
  30 /**
  31  * @author cmathew
  32  * @date 1 Apr 2015
  33  *
  34  */
  35 public class CdmQueryFactory {
  36
  37
  38         public static final String DTYPE_COLUMN = "DTYPE";
  39         public static final String ID_COLUMN = "id";
  40         public static final String UUID_COLUMN = "uuid";
  41         public static final String CLASSIFICATION_COLUMN = "classification";
  42         public static final String RANK_COLUMN = "Rang";
  43         public static final String TAXON_COLUMN = "Taxon";
  44
  45     public static final String ID = "id";
  46     public static final String UUID_ID = "uuid";
  47
  48     public static QueryDelegate generateTaxonTreeQuery(String name_id, String classificationId)  {
  49         String FROM_QUERY = " FROM TaxonBase tb " +
  50                 "INNER JOIN TaxonNode tn on tn.taxon_id=tb.id " +
  51                 "INNER JOIN TaxonName n on tb.name_id=n.id " +
  52                 "INNER JOIN Classification cl on cl.id=tn.classification_id and cl.id='"+classificationId+"'";
  53         String SELECT_QUERY="SELECT tn.id as " + ID +
  54                 ", tb.uuid as " + UUID_ID +
  55                 ", n.titleCache as " + name_id +
  56                 ", tn.parent_id as parent" +
  57                 FROM_QUERY;
  58         String COUNT_QUERY = "SELECT count(*) " + FROM_QUERY;
  59         String CONTAINS_QUERY = "SELECT * FROM TaxonBase tb WHERE tb.id = ?";
  60
  61         return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
  62     }
  63
  64     public static QueryDelegate generateTaxonBaseQuery(String name_id,
  65             String pb_id,
  66             String unp_id,
  67             String rank_id,
  68             String has_syn_id)  {
  69         String FROM_QUERY = " FROM TaxonBase tb " +
  70                 "INNER JOIN TaxonNode tn on tn.taxon_id=tb.id " +
  71                 "INNER JOIN TaxonName n on tb.name_id=n.id " +
  72                 "INNER JOIN DefinedTermBase dtb on n.rank_id = dtb.id";
  73         String SELECT_QUERY="SELECT tb.id as " + ID +
  74                 ", tb.uuid as " + UUID_ID +
  75                 ", n.titleCache as " + name_id +
  76                 ", tb.publish as " + pb_id +
  77                 ", tn.unplaced as " + unp_id +
  78                 ", dtb.titleCache as " + rank_id +
  79                 ", (SELECT COUNT(*) FROM TaxonBase syn WHERE tb.id = syn.acceptedTaxon_id) as " + has_syn_id +
  80                 FROM_QUERY;
  81         String COUNT_QUERY = "SELECT count(*) " + FROM_QUERY;
  82         String CONTAINS_QUERY = "SELECT * FROM TaxonBase tb WHERE tb.id = ?";
  83
  84         return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
  85     }
  86
  87     public static QueryDelegate generateTaxonDistributionQuery(List<Integer> taxonNodeIds, Collection<NamedArea> namedAreas) {
  88
  89         String idString = "";
  90         Iterator<Integer> nodeIterator = taxonNodeIds.iterator();
  91         while (nodeIterator.hasNext()) {
  92                         Integer integer = nodeIterator.next();
  93                         idString += String.valueOf(integer);
  94                         if(nodeIterator.hasNext()){
  95                                 idString += ", ";
  96                         }
  97                 }
  98         String FROM_QUERY =
  99                 "FROM TaxonName n "
 100                 + "INNER JOIN TaxonBase tb on n.id = tb.name_id and tb.DTYPE='Taxon' " + // # name<->taxon
 101         "INNER JOIN TaxonNode tn on tn.taxon_id = tb.id "+
 102         "INNER JOIN DefinedTermBase rank on n.rank_id = rank.id "+// # rank <-> name
 103         "LEFT OUTER JOIN DescriptionBase descr on descr.taxon_id = tb.id "+// # taxon <-> taxon description (not every taxon has a description)
 104         "LEFT OUTER JOIN DescriptionElementBase descrEl on descrEl.indescription_id = descr.id and descrEl.DTYPE = 'Distribution' "+// # distribution <-> description
 105         "LEFT OUTER JOIN DefinedTermBase statusTerm on statusTerm.id = descrEl.status_id "+
 106         "LEFT OUTER JOIN DefinedTermBase area on area.id = descrEl.area_id ";
 107         if(CdmUtils.isNotBlank(idString)){
 108                 FROM_QUERY += "WHERE tn.id IN ("+ idString +") ";
 109         }
 110
 111         String GROUP_BY = " GROUP BY tb.uuid, tn.id ";
 112
 113         String ORDER_BY = " ORDER BY tb.titleCache ";
 114
 115         String SELECT_QUERY= "SELECT "
 116                 + "tb.DTYPE AS "+DTYPE_COLUMN+", "
 117                 + "tb.id AS "+ID_COLUMN+", "
 118                 + "tb.uuid AS "+UUID_COLUMN+", "
 119                 + "tn.classification_id AS "+CLASSIFICATION_COLUMN+", "+
 120                         "tb.titleCache AS "+TAXON_COLUMN+", " +
 121                         "rank.titleCache AS "+RANK_COLUMN+", ";
 122
 123         Map<String, Integer> labels = new HashMap<>();
 124         for(NamedArea namedArea : namedAreas){
 125             String label = null;
 126             String fullLabel = null;
 127             String abbreviatedLabel = null;
 128             Representation representation = namedArea.getRepresentation(Language.DEFAULT());
 129             if(representation!=null){
 130                 fullLabel = representation.getLabel();
 131                                 abbreviatedLabel = representation.getAbbreviatedLabel();
 132                                 if(DistributionEditorUtil.isAbbreviatedLabels()){
 133                         label = abbreviatedLabel;
 134                 }
 135                 else{
 136                         label = fullLabel;
 137                 }
 138             }
 139             //fallback
 140             if(label==null){
 141                 label = namedArea.getTitleCache();
 142             }
 143
 144             //check if label already exists
 145             Integer count = labels.get(label);
 146             if(count!=null){
 147                 //combine label and abbreviated and check again
 148                 if(abbreviatedLabel!=null && fullLabel!= null){
 149                         label = abbreviatedLabel+"-"+fullLabel;
 150                 }
 151             }
 152             count = labels.get(label);
 153             if(count==null){
 154                 labels.put(label, 1);
 155             }
 156             else{
 157                 labels.put(label, count+1);
 158                 label += "("+count+")";
 159             }
 160             SELECT_QUERY += "MAX( IF(area.uuid = '"+ namedArea.getUuid() +"', statusTerm.titleCache, NULL) ) as '"+ label +"'," ;
 161         }
 162         SELECT_QUERY = StringUtils.stripEnd(SELECT_QUERY, ",")+" ";
 163         SELECT_QUERY= SELECT_QUERY + FROM_QUERY + GROUP_BY + ORDER_BY;
 164         String COUNT_QUERY = "SELECT count(DISTINCT tb.id)" + FROM_QUERY;
 165         String CONTAINS_QUERY = "SELECT * FROM TaxonBase tb WHERE tb.uuid = ?";
 166         //Escape SQL control character '
 167         Pattern p = Pattern.compile("(\\w+)'(\\w+)");
 168         Matcher m = p.matcher(SELECT_QUERY);
 169         if (m.find()) {
 170             SELECT_QUERY = m.replaceAll("$1\\\\'$2");
 171         }
 172         return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
 173     }
 174
 175     public static QueryDelegate generateSynonymOfTaxonQuery(String name_id)  {
 176         String FROM_QUERY = " FROM TaxonBase tb " +
 177                         "INNER JOIN TaxonName n on tb.name_id=n.id " +
 178                         "INNER JOIN TaxonBase acc on tb.acceptedTaxon_id = acc.id "; //or s.id = ?
 179         String SELECT_QUERY="SELECT tb.id as " + ID +
 180                         ", n.titleCache as " + name_id +
 181                         FROM_QUERY;
 182         String COUNT_QUERY = "SELECT count(*) " + FROM_QUERY;
 183         String CONTAINS_QUERY = "SELECT * FROM TaxonBase syn WHERE syn.id = ?"; //or s.id = ?
 184
 185         return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
 186     }
 187
 188     public static QueryDelegate generateTaxonRelatedToQuery(String reluuid_id,
 189             String reltype_id,
 190             String to_id,
 191             String touuid_id,
 192             String toname_id)  {
 193         String FROM_QUERY = "     FROM TaxonRelationship tr " +
 194                 "INNER JOIN TaxonBase tb on tr.relatedto_id = tb.id " +
 195                 "INNER JOIN TaxonNode tn on tb.id = tn.taxon_id ";
 196         String SELECT_QUERY= "SELECT tr.id as " + ID +
 197                 ", tr.uuid as " + reluuid_id +
 198                 ", tr.type_id as " + reltype_id +
 199                 ", tr.relatedto_id as " + to_id +
 200                 ", tb.uuid as " + touuid_id +
 201                 ", tb.titleCache as " + toname_id +
 202                 FROM_QUERY;
 203         String COUNT_QUERY = "SELECT count(*) " + FROM_QUERY;
 204         String CONTAINS_QUERY = "SELECT * FROM TaxonRelationship tr where tr.relatedfrom_id = ?";
 205
 206         return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
 207     }
 208
 209     /**
 210      * Creates a FreeformQuery which mimics a TableQuery.
 211      * This method works around the bug at http://dev.vaadin.com/ticket/12370
 212      *
 213      * @param tableName
 214      * @return
 215      */
 216     public static QueryDelegate generateTableQuery(String tableName) {
 217         String FROM_QUERY = " FROM " + tableName;
 218         String SELECT_QUERY=" SELECT * " +
 219                 FROM_QUERY;
 220         String COUNT_QUERY = "SELECT count(*) " + FROM_QUERY;
 221         String CONTAINS_QUERY = "SELECT * FROM " + tableName + "  WHERE id = ?";
 222
 223         return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
 224     }
 225
 226     public static QueryDelegate generateQueryDelegate(String SELECT_QUERY, String COUNT_QUERY, String CONTAINS_QUERY) {
 227         FreeformQuery query = new FreeformQuery("This query is not used", CdmSpringContextHelper.getCurrent().getConnectionPool(), ID);
 228         CdmStatementDelegate delegate = new CdmStatementDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
 229         query.setDelegate(delegate);
 230         return query;
 231     }
 232 }