2 * Copyright (C) 2015 EDIT
3 * European Distributed Institute of Taxonomy
4 * http://www.e-taxonomy.eu
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.
9 package eu
.etaxonomy
.cdm
.vaadin
.util
;
11 import java
.util
.Collection
;
12 import java
.util
.HashMap
;
13 import java
.util
.Iterator
;
14 import java
.util
.List
;
16 import java
.util
.regex
.Matcher
;
17 import java
.util
.regex
.Pattern
;
19 import org
.apache
.commons
.lang
.StringUtils
;
21 import com
.vaadin
.data
.util
.sqlcontainer
.query
.FreeformQuery
;
22 import com
.vaadin
.data
.util
.sqlcontainer
.query
.QueryDelegate
;
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
;
35 public class CdmQueryFactory
{
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
= "RANK";
43 public static final String TAXON_COLUMN
= "TAXON";
45 public static final String ID
= "ID";
46 public static final String UUID_ID
= "UUID";
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" +
58 String COUNT_QUERY
= "SELECT count(*) " + FROM_QUERY
;
59 String CONTAINS_QUERY
= "SELECT * FROM TaxonBase tb WHERE tb.id = ?";
61 return generateQueryDelegate(SELECT_QUERY
, COUNT_QUERY
, CONTAINS_QUERY
);
64 public static QueryDelegate
generateTaxonBaseQuery(String name_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
+
81 String COUNT_QUERY
= "SELECT count(*) " + FROM_QUERY
;
82 String CONTAINS_QUERY
= "SELECT * FROM TaxonBase tb WHERE tb.id = ?";
84 return generateQueryDelegate(SELECT_QUERY
, COUNT_QUERY
, CONTAINS_QUERY
);
87 public static QueryDelegate
generateTaxonDistributionQuery(List
<Integer
> taxonNodeIds
, Collection
<NamedArea
> namedAreas
) {
90 Iterator
<Integer
> nodeIterator
= taxonNodeIds
.iterator();
91 while (nodeIterator
.hasNext()) {
92 Integer integer
= nodeIterator
.next();
93 idString
+= String
.valueOf(integer
);
94 if(nodeIterator
.hasNext()){
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
+") ";
111 String GROUP_BY
= " GROUP BY tb.uuid, tn.id ";
113 String ORDER_BY
= " ORDER BY tb.titleCache ";
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
+", ";
123 Map
<String
, Integer
> labels
= new HashMap
<>();
124 for(NamedArea namedArea
: namedAreas
){
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
;
141 label
= namedArea
.getTitleCache();
144 //check if label already exists
145 Integer count
= labels
.get(label
);
147 //combine label and abbreviated and check again
148 if(abbreviatedLabel
!=null && fullLabel
!= null){
149 label
= abbreviatedLabel
+"-"+fullLabel
;
152 count
= labels
.get(label
);
154 labels
.put(label
, 1);
157 labels
.put(label
, count
+1);
158 label
+= "("+count
+")";
160 SELECT_QUERY
+= "MAX( IF(area.uuid = '"+ namedArea
.getUuid() +"', statusTerm.uuid, NULL) ) as '"+ label
+"'," ;
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
);
170 SELECT_QUERY
= m
.replaceAll("$1\\\\'$2");
172 return generateQueryDelegate(SELECT_QUERY
, COUNT_QUERY
, CONTAINS_QUERY
);
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
+
182 String COUNT_QUERY
= "SELECT count(*) " + FROM_QUERY
;
183 String CONTAINS_QUERY
= "SELECT * FROM TaxonBase syn WHERE syn.id = ?"; //or s.id = ?
185 return generateQueryDelegate(SELECT_QUERY
, COUNT_QUERY
, CONTAINS_QUERY
);
188 public static QueryDelegate
generateTaxonRelatedToQuery(String reluuid_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
+
203 String COUNT_QUERY
= "SELECT count(*) " + FROM_QUERY
;
204 String CONTAINS_QUERY
= "SELECT * FROM TaxonRelationship tr where tr.relatedfrom_id = ?";
206 return generateQueryDelegate(SELECT_QUERY
, COUNT_QUERY
, CONTAINS_QUERY
);
210 * Creates a FreeformQuery which mimics a TableQuery.
211 * This method works around the bug at http://dev.vaadin.com/ticket/12370
216 public static QueryDelegate
generateTableQuery(String tableName
) {
217 String FROM_QUERY
= " FROM " + tableName
;
218 String SELECT_QUERY
=" SELECT * " +
220 String COUNT_QUERY
= "SELECT count(*) " + FROM_QUERY
;
221 String CONTAINS_QUERY
= "SELECT * FROM " + tableName
+ " WHERE id = ?";
223 return generateQueryDelegate(SELECT_QUERY
, COUNT_QUERY
, CONTAINS_QUERY
);
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
);