cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/persistence/dao/hibernate/taxon/TaxonDaoHibernateImpl.java

   1 /**
   2  * Copyright (C) 2007 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.persistence.dao.hibernate.taxon;
  10
  11 import java.util.ArrayList;
  12 import java.util.Collection;
  13 import java.util.Collections;
  14 import java.util.Comparator;
  15 import java.util.HashSet;
  16 import java.util.Iterator;
  17 import java.util.List;
  18 import java.util.Set;
  19 import java.util.SortedSet;
  20 import java.util.TreeSet;
  21 import java.util.UUID;
  22
  23 import org.apache.commons.lang.StringUtils;
  24 import org.apache.log4j.Logger;
  25 import org.hibernate.Criteria;
  26 import org.hibernate.FetchMode;
  27 import org.hibernate.Hibernate;
  28 import org.hibernate.Query;
  29 import org.hibernate.Session;
  30 import org.hibernate.criterion.Criterion;
  31 import org.hibernate.criterion.Order;
  32 import org.hibernate.criterion.Projections;
  33 import org.hibernate.criterion.Restrictions;
  34 import org.hibernate.envers.query.AuditEntity;
  35 import org.hibernate.envers.query.AuditQuery;
  36 import org.hibernate.envers.query.criteria.internal.NotNullAuditExpression;
  37 import org.hibernate.envers.query.internal.property.EntityPropertyName;
  38 import org.hibernate.search.FullTextSession;
  39 import org.hibernate.search.Search;
  40 import org.springframework.beans.factory.annotation.Autowired;
  41 import org.springframework.beans.factory.annotation.Qualifier;
  42 import org.springframework.dao.DataAccessException;
  43 import org.springframework.stereotype.Repository;
  44
  45 import eu.etaxonomy.cdm.model.common.DefinedTerm;
  46 import eu.etaxonomy.cdm.model.common.IdentifiableEntity;
  47 import eu.etaxonomy.cdm.model.common.IdentifiableSource;
  48 import eu.etaxonomy.cdm.model.common.LSID;
  49 import eu.etaxonomy.cdm.model.common.MarkerType;
  50 import eu.etaxonomy.cdm.model.common.RelationshipBase.Direction;
  51 import eu.etaxonomy.cdm.model.location.NamedArea;
  52 import eu.etaxonomy.cdm.model.name.Rank;
  53 import eu.etaxonomy.cdm.model.name.TaxonName;
  54 import eu.etaxonomy.cdm.model.name.TaxonNameComparator;
  55 import eu.etaxonomy.cdm.model.reference.Reference;
  56 import eu.etaxonomy.cdm.model.taxon.Classification;
  57 import eu.etaxonomy.cdm.model.taxon.Synonym;
  58 import eu.etaxonomy.cdm.model.taxon.SynonymType;
  59 import eu.etaxonomy.cdm.model.taxon.Taxon;
  60 import eu.etaxonomy.cdm.model.taxon.TaxonBase;
  61 import eu.etaxonomy.cdm.model.taxon.TaxonNode;
  62 import eu.etaxonomy.cdm.model.taxon.TaxonRelationship;
  63 import eu.etaxonomy.cdm.model.taxon.TaxonRelationshipType;
  64 import eu.etaxonomy.cdm.model.taxon.UuidAndTitleCacheTaxonComparator;
  65 import eu.etaxonomy.cdm.model.view.AuditEvent;
  66 import eu.etaxonomy.cdm.persistence.dao.hibernate.common.IdentifiableDaoBase;
  67 import eu.etaxonomy.cdm.persistence.dao.name.ITaxonNameDao;
  68 import eu.etaxonomy.cdm.persistence.dao.taxon.ITaxonDao;
  69 import eu.etaxonomy.cdm.persistence.dto.TaxonGraphEdgeDTO;
  70 import eu.etaxonomy.cdm.persistence.dto.UuidAndTitleCache;
  71 import eu.etaxonomy.cdm.persistence.query.MatchMode;
  72 import eu.etaxonomy.cdm.persistence.query.NameSearchOrder;
  73 import eu.etaxonomy.cdm.persistence.query.OrderHint;
  74 import eu.etaxonomy.cdm.persistence.query.TaxonTitleType;
  75
  76
  77 /**
  78  * @author a.mueller
  79  * @since 24.11.2008
  80  */
  81 @Repository
  82 @Qualifier("taxonDaoHibernateImpl")
  83 public class TaxonDaoHibernateImpl
  84               extends IdentifiableDaoBase<TaxonBase>
  85               implements ITaxonDao {
  86 //    private AlternativeSpellingSuggestionParser<TaxonBase> alternativeSpellingSuggestionParser;
  87     private static final Logger logger = Logger.getLogger(TaxonDaoHibernateImpl.class);
  88
  89     public TaxonDaoHibernateImpl() {
  90         super(TaxonBase.class);
  91         indexedClasses = new Class[2];
  92         indexedClasses[0] = Taxon.class;
  93         indexedClasses[1] = Synonym.class;
  94         super.defaultField = "name.titleCache_tokenized";
  95     }
  96
  97     @Autowired
  98     private ITaxonNameDao taxonNameDao;
  99
 100 ////    spelling support currently disabled in appcontext, see spelling.xml ... "
 101 ////    @Autowired(required = false)   //TODO switched of because it caused problems when starting CdmApplicationController
 102 //    public void setAlternativeSpellingSuggestionParser(AlternativeSpellingSuggestionParser<TaxonBase> alternativeSpellingSuggestionParser) {
 103 //        this.alternativeSpellingSuggestionParser = alternativeSpellingSuggestionParser;
 104 //    }
 105
 106     @Override
 107     public TaxonBase load(UUID uuid, List<String> propertyPaths){
 108         return load(uuid, INCLUDE_UNPUBLISHED, propertyPaths);
 109     }
 110
 111     @Override
 112     public TaxonBase load(UUID uuid, boolean includeUnpublished, List<String> propertyPaths){
 113         TaxonBase<?> result = super.load(uuid, includeUnpublished, propertyPaths);
 114         return result; //(result == null || (!result.isPublish() && !includeUnpublished))? null : result;
 115     }
 116
 117
 118     @Override
 119     public List<TaxonBase> getTaxaByName(String queryString, boolean includeUnpublished, Reference sec) {
 120
 121         return getTaxaByName(queryString, true, includeUnpublished, sec);
 122     }
 123
 124     @Override
 125     public List<TaxonBase> getTaxaByName(String queryString, Boolean accepted, boolean includeUnpublished, Reference sec) {
 126         checkNotInPriorView("TaxonDaoHibernateImpl.getTaxaByName(String name, Reference sec)");
 127
 128         Criteria criteria = null;
 129         if (accepted == true) {
 130             criteria = getSession().createCriteria(Taxon.class);
 131         } else {
 132             criteria = getSession().createCriteria(Synonym.class);
 133         }
 134
 135         criteria.setFetchMode( "name", FetchMode.JOIN );
 136         criteria.createAlias("name", "name");
 137
 138         if (!includeUnpublished){
 139             criteria.add(Restrictions.eq("publish", Boolean.TRUE ));
 140         }
 141
 142         if (sec != null && sec.getId() != 0) {
 143             criteria.add(Restrictions.eq("sec", sec ) );
 144         }
 145
 146         if (queryString != null) {
 147             criteria.add(Restrictions.ilike("name.nameCache", queryString));
 148         }
 149
 150         @SuppressWarnings({ "unchecked", "rawtypes" })
 151         List<TaxonBase> result = criteria.list();
 152         return result;
 153     }
 154
 155     //TODO needed? Currently only used by tests.
 156     public List<TaxonBase> getTaxaByName(boolean doTaxa, boolean doSynonyms, boolean includeUnpublished,
 157             String queryString, MatchMode matchMode, Integer pageSize, Integer pageNumber) {
 158         return getTaxaByName(doTaxa, doSynonyms, false, false, false,
 159                 queryString, null, null, matchMode, null, includeUnpublished, null, pageSize, pageNumber, null);
 160     }
 161
 162     @Override
 163     public List<TaxonBase> getTaxaByName(String queryString, MatchMode matchMode,
 164             Boolean accepted, boolean includeUnpublished, Integer pageSize, Integer pageNumber) {
 165
 166         boolean doTaxa = true;
 167         boolean doSynonyms = true;
 168
 169         if (accepted == true) {
 170             doSynonyms = false;
 171         } else {
 172            doTaxa = false;
 173         }
 174         return getTaxaByName(doTaxa, doSynonyms, includeUnpublished, queryString, matchMode, pageSize, pageNumber);
 175     }
 176
 177     @Override
 178     public List<TaxonBase> getTaxaByName(boolean doTaxa, boolean doSynonyms, boolean doMisappliedNames, boolean doCommonNames,
 179             boolean includeAuthors,
 180             String queryString, Classification classification, TaxonNode subtree,
 181             MatchMode matchMode, Set<NamedArea> namedAreas, boolean includeUnpublished, NameSearchOrder order,
 182             Integer pageSize, Integer pageNumber, List<String> propertyPaths) {
 183
 184         boolean doCount = false;
 185
 186         String searchField = includeAuthors ? "titleCache" : "nameCache";
 187         Query query = prepareTaxaByName(doTaxa, doSynonyms, doMisappliedNames, doCommonNames, includeUnpublished, searchField, queryString, classification, subtree, matchMode, namedAreas, order, pageSize, pageNumber, doCount);
 188
 189         if (query != null){
 190             @SuppressWarnings({ "unchecked", "rawtypes" })
 191             List<TaxonBase> results = query.list();
 192
 193             defaultBeanInitializer.initializeAll(results, propertyPaths);
 194
 195             //Collections.sort(results, comp);
 196             return results;
 197         }else{
 198             return new ArrayList<>();
 199         }
 200     }
 201
 202     //new search for the editor, for performance issues the return values are only uuid and titleCache, to avoid the initialisation of all objects
 203     @Override
 204     @SuppressWarnings("unchecked")
 205     public List<UuidAndTitleCache<? extends IdentifiableEntity>> getTaxaByNameForEditor(boolean doTaxa, boolean doSynonyms, boolean doNamesWithoutTaxa,
 206             boolean doMisappliedNames, boolean doCommonNames, boolean includeUnpublished, String queryString, Classification classification, TaxonNode subtree,
 207             MatchMode matchMode, Set<NamedArea> namedAreas, NameSearchOrder order) {
 208
 209         if (order == null){
 210             order = NameSearchOrder.ALPHA;  //TODO add to signature
 211         }
 212
 213         boolean doCount = false;
 214         boolean includeAuthors = false;
 215         @SuppressWarnings("rawtypes")
 216         List<UuidAndTitleCache<? extends IdentifiableEntity>> resultObjects = new ArrayList<>();
 217         if (doNamesWithoutTaxa){
 218                 List<TaxonName> nameResult = taxonNameDao.findByName(
 219                         includeAuthors, queryString, matchMode, null, null, null, null);
 220
 221                 for (TaxonName name: nameResult){
 222                         if (name.getTaxonBases().size() == 0){
 223                                 resultObjects.add(new UuidAndTitleCache<>(TaxonName.class, name.getUuid(),
 224                                         name.getId(), name.getTitleCache()));
 225                         }
 226                 }
 227                 if (!doSynonyms && !doTaxa && !doCommonNames){
 228                         return resultObjects;
 229                 }
 230         }
 231         Query query = prepareTaxaByNameForEditor(doTaxa, doSynonyms, doMisappliedNames, doCommonNames, includeUnpublished,
 232                 "nameCache", queryString, classification, subtree, matchMode, namedAreas, doCount, order);
 233
 234         if (query != null){
 235             List<Object[]> results = query.list();
 236
 237             Object[] result;
 238             for(int i = 0; i<results.size();i++){
 239                 result = results.get(i);
 240
 241                 //differentiate taxa and synonyms
 242                 // new Boolean(result[3].toString()) is due to the fact that result[3] could be a Boolean ora String
 243                 // see FIXME in 'prepareQuery' for more details
 244                 if (doTaxa || doSynonyms || doCommonNames){
 245                     if (result[3].equals("synonym")) {
 246                         resultObjects.add( new UuidAndTitleCache<>(Synonym.class, (UUID) result[0], (Integer) result[1], (String)result[2], new Boolean(result[4].toString()), null));
 247                     }
 248                     else {
 249                         resultObjects.add( new UuidAndTitleCache<>(Taxon.class, (UUID) result[0], (Integer) result[1], (String)result[2], new Boolean(result[4].toString()), null));
 250                     }
 251
 252                 }else if (doSynonyms){
 253                     resultObjects.add( new UuidAndTitleCache<>(Synonym.class, (UUID) result[0], (Integer) result[1], (String)result[2], new Boolean(result[4].toString()), null));
 254                 }
 255             }
 256         }
 257         return resultObjects;
 258
 259     }
 260
 261     @Override
 262     public List<Taxon> getTaxaByCommonName(String queryString, Classification classification,
 263                MatchMode matchMode, Set<NamedArea> namedAreas, Integer pageSize,
 264                Integer pageNumber, List<String> propertyPaths) {
 265         boolean doCount = false;
 266         Query query = prepareTaxaByCommonName(queryString, classification, matchMode, namedAreas, pageSize, pageNumber, doCount, false);
 267         if (query != null){
 268             @SuppressWarnings("unchecked")
 269             List<Taxon> results = query.list();
 270             defaultBeanInitializer.initializeAll(results, propertyPaths);
 271             return results;
 272         }else{
 273             return new ArrayList<>();
 274         }
 275
 276     }
 277
 278     /**
 279      * @param clazz
 280      * @param searchField the field in TaxonName to be searched through usually either <code>nameCache</code> or <code>titleCache</code>
 281      * @param queryString
 282      * @param classification TODO
 283      * @param matchMode
 284      * @param namedAreas
 285      * @param pageSize
 286      * @param pageNumber
 287      * @param doCount
 288      * @return
 289      *
 290      *
 291      */
 292     private Query prepareTaxaByNameForEditor(boolean doTaxa, boolean doSynonyms, boolean doMisappliedNames, boolean doCommonNames,
 293             boolean includeUnpublished, String searchField, String queryString, Classification classification, TaxonNode subtree,
 294             MatchMode matchMode, Set<NamedArea> namedAreas, boolean doCount, NameSearchOrder order) {
 295         return prepareByNameQuery(doTaxa, doSynonyms, doMisappliedNames, doCommonNames, includeUnpublished,
 296                 searchField, queryString,
 297                 classification, subtree, matchMode, namedAreas, order, doCount, true);
 298     }
 299
 300
 301     /**
 302      * @param doTaxa
 303      * @param doSynonyms
 304      * @param doIncludeMisappliedNames
 305      * @param doCommonNames
 306      * @param includeUnpublished
 307      * @param searchField
 308      * @param queryString
 309      * @param classification
 310      * @param matchMode
 311      * @param namedAreas
 312      * @param order
 313      * @param doCount
 314      * @param returnIdAndTitle
 315      *            If set true the seach method will not return synonym and taxon
 316      *            entities but an array containing the uuid, titleCache, and the
 317      *            DTYPE in lowercase letters.
 318      * @return
 319      */
 320     private Query prepareByNameQuery(boolean doTaxa, boolean doSynonyms, boolean doMisappliedNames,
 321                 boolean doCommonNames, boolean includeUnpublished, String searchField, String queryString,
 322                 Classification classification, TaxonNode subtree, MatchMode matchMode, Set<NamedArea> namedAreas,
 323                 NameSearchOrder order, boolean doCount, boolean returnIdAndTitle){
 324
 325             boolean doProParteSynonyms = doSynonyms;  //we may distinguish in future
 326             boolean doConceptRelations = doMisappliedNames || doProParteSynonyms;
 327
 328             if (order == null){
 329                 order = NameSearchOrder.DEFAULT();
 330             }
 331             String hqlQueryString = matchMode.queryStringFrom(queryString);
 332             String selectWhat;
 333             if (returnIdAndTitle){
 334                 selectWhat = "t.uuid, t.id, t.titleCache ";
 335             }else {
 336                 selectWhat = (doCount ? "count(t)": "t");
 337             }
 338
 339             //area filter
 340             //TODO share code with taxon node filter
 341             String hql = "";
 342             Set<NamedArea> areasExpanded = new HashSet<>();
 343             if(namedAreas != null && namedAreas.size() > 0){
 344                 // expand areas and restrict by distribution area
 345                 Query areaQuery = getSession().createQuery("SELECT childArea "
 346                         + " FROM NamedArea AS childArea LEFT JOIN childArea.partOf as parentArea "
 347                         + " WHERE parentArea = :area");
 348                 expandNamedAreas(namedAreas, areasExpanded, areaQuery);
 349             }
 350             boolean doAreaRestriction = areasExpanded.size() > 0;
 351
 352             Set<UUID> namedAreasUuids = new HashSet<>();
 353             for (NamedArea area:areasExpanded){
 354                 namedAreasUuids.add(area.getUuid());
 355             }
 356
 357             Subselects subSelects = createByNameHQLString(doConceptRelations,
 358                     includeUnpublished, classification, subtree, areasExpanded, matchMode, searchField);
 359             String taxonSubselect = subSelects.taxonSubselect;
 360             String synonymSubselect = subSelects.synonymSubselect;
 361             String conceptSelect = subSelects.conceptSelect;
 362             String commonNameSubSelect = subSelects.commonNameSubselect;
 363
 364
 365             if (logger.isDebugEnabled()) {
 366                 logger.debug("taxonSubselect: " + (taxonSubselect != null ? taxonSubselect: "NULL"));
 367                 logger.debug("synonymSubselect: " + (synonymSubselect != null ? synonymSubselect: "NULL"));
 368             }
 369
 370             List<Integer> taxonIDs = new ArrayList<>();
 371             List<Integer> synonymIDs = new ArrayList<>();
 372
 373             if(doTaxa){
 374                 // find Taxa
 375                 Query subTaxon = getSearchQueryString(hqlQueryString, taxonSubselect);
 376
 377                 addRestrictions(doAreaRestriction, classification, subtree, includeUnpublished,
 378                         namedAreasUuids, subTaxon);
 379                 taxonIDs = subTaxon.list();
 380             }
 381
 382             if(doSynonyms){
 383                 // find synonyms
 384                 Query subSynonym = getSearchQueryString(hqlQueryString, synonymSubselect);
 385                 addRestrictions(doAreaRestriction, classification, subtree, includeUnpublished, namedAreasUuids,subSynonym);
 386                 synonymIDs = subSynonym.list();
 387             }
 388             if (doConceptRelations ){
 389                 Query subMisappliedNames = getSearchQueryString(hqlQueryString, conceptSelect);
 390                 Set<TaxonRelationshipType> relTypeSet = new HashSet<>();
 391                 if (doMisappliedNames){
 392                     relTypeSet.addAll(TaxonRelationshipType.allMisappliedNameTypes());
 393                 }
 394                 if (doProParteSynonyms){
 395                     relTypeSet.addAll(TaxonRelationshipType.allSynonymTypes());
 396                 }
 397                 subMisappliedNames.setParameterList("rTypeSet", relTypeSet);
 398                 addRestrictions(doAreaRestriction, classification, subtree, includeUnpublished, namedAreasUuids, subMisappliedNames);
 399                 taxonIDs.addAll(subMisappliedNames.list());
 400             }
 401
 402             if(doCommonNames){
 403                 // find Taxa
 404                 Query subCommonNames = getSearchQueryString(hqlQueryString, commonNameSubSelect);
 405                 addRestrictions(doAreaRestriction, classification, subtree, includeUnpublished, namedAreasUuids, subCommonNames);
 406                 taxonIDs.addAll(subCommonNames.list());
 407             }
 408
 409
 410             if(synonymIDs.size()>0 && taxonIDs.size()>0){
 411                 hql = "SELECT " + selectWhat;
 412                 // in doNotReturnFullEntities mode it is nesscary to also return the type of the matching entities:
 413                 // also return the computed isOrphaned flag
 414                 if (returnIdAndTitle &&  !doCount ){
 415                     hql += ", CASE WHEN t.id in (:taxa) THEN 'taxon' ELSE 'synonym' END, " +
 416                             " CASE WHEN t.id in (:taxa) "
 417                                     + " AND t.taxonNodes IS EMPTY "
 418                                     + " AND t.relationsFromThisTaxon IS EMPTY "
 419                                     + " AND t.relationsToThisTaxon IS EMPTY "
 420                                  + " THEN true ELSE false END ";
 421                 }
 422                 hql +=  " FROM %s t " +
 423                         " WHERE (t.id in (:taxa) OR t.id IN (:synonyms)) ";
 424             }else if (synonymIDs.size()>0 ){
 425                 hql = "SELECT " + selectWhat;
 426                 // in doNotReturnFullEntities mode it is nesscary to also return the type of the matching entities:
 427                 // also return the computed isOrphaned flag
 428                 if (returnIdAndTitle &&  !doCount ){
 429                     hql += ", 'synonym', 'false' ";
 430
 431                 }
 432                 hql +=  " FROM %s t " +
 433                         " WHERE t.id in (:synonyms) ";
 434
 435             } else if (taxonIDs.size()>0 ){
 436                 hql = "SELECT " + selectWhat;
 437                 // in doNotReturnFullEntities mode it is nesscary to also return the type of the matching entities:
 438                 // also return the computed isOrphaned flag
 439                 if (returnIdAndTitle &&  !doCount ){
 440                     hql += ", 'taxon', " +
 441                             " CASE WHEN t.taxonNodes is empty "
 442                             + "  AND t.relationsFromThisTaxon is empty "
 443                             + "  AND t.relationsToThisTaxon is empty "
 444                             + "THEN true ELSE false END ";
 445                 }
 446                 hql +=  " FROM %s t " +
 447                         " WHERE t.id in (:taxa) ";
 448             } else if (StringUtils.isBlank(queryString)){
 449                 hql = "SELECT " + selectWhat + " FROM %s t";
 450             } else{
 451                 return null;
 452             }
 453
 454             String classString;
 455             if ((doTaxa || doCommonNames || doConceptRelations) && doSynonyms){
 456                 classString = "TaxonBase";
 457             } else if (doTaxa || doCommonNames){
 458                 classString = "Taxon";
 459             } else if (doSynonyms && !(doCommonNames|| doTaxa || doConceptRelations)){
 460                 classString = "Synonym";  // as long as doProParteSynonyms = doSynonyms this case should not happen
 461             } else{//only misappliedNames
 462                 classString = "Taxon";
 463             }
 464
 465             hql = String.format(hql, classString);
 466
 467             if (hql == "") {
 468                 return null;
 469             }
 470             if(!doCount){
 471                 String orderBy = " ORDER BY ";
 472                 String alphabeticBase = " t.name.genusOrUninomial, case when t.name.specificEpithet like '\"%\"' then 1 else 0 end, t.name.specificEpithet, t.name.rank desc, t.name.nameCache";
 473
 474                 if (order == NameSearchOrder.LENGTH_ALPHA_NAME){
 475                     orderBy += " length(t.name.nameCache), " + alphabeticBase;
 476                 }else if (order == NameSearchOrder.LENGTH_ALPHA_TITLE){
 477                     orderBy += " length(t.name.titleCache), " + alphabeticBase;
 478                 }else {
 479                     orderBy += alphabeticBase;
 480                 }
 481
 482                 hql += orderBy;
 483             }
 484
 485             if(logger.isDebugEnabled()){ logger.debug("hql: " + hql);}
 486             Query query = getSession().createQuery(hql);
 487
 488             // find taxa and synonyms
 489             if (taxonIDs.size()>0){
 490                 query.setParameterList("taxa", taxonIDs);
 491             }
 492             if (synonymIDs.size()>0){
 493                 query.setParameterList("synonyms",synonymIDs);
 494             }
 495             if (taxonIDs.size()== 0 && synonymIDs.size() == 0){
 496                 return null;
 497             }
 498
 499             return query;
 500     }
 501
 502     /**
 503      * @param hqlQueryString
 504      * @param synonymSubselect
 505      * @return
 506      */
 507     protected Query getSearchQueryString(String hqlQueryString, String synonymSubselect) {
 508         return getSession().createQuery(synonymSubselect).setParameter("queryString", hqlQueryString);
 509     }
 510
 511     /**
 512      * @param includeUnpublished
 513      * @param classification
 514      * @param doAreaRestriction
 515      * @param subtree
 516      * @param namedAreasUuids
 517      * @param subTaxon
 518      */
 519     protected void addRestrictions(boolean doAreaRestriction, Classification classification, TaxonNode subtree, boolean includeUnpublished,
 520             Set<UUID> namedAreasUuids, Query query) {
 521         if(doAreaRestriction){
 522             query.setParameterList("namedAreasUuids", namedAreasUuids);
 523         }
 524         if(classification != null){
 525             query.setParameter("classification", classification);
 526         }
 527         if(subtree != null){
 528             query.setParameter("treeIndexLike", subtree.treeIndex() + "%");
 529         }
 530         if(!includeUnpublished){
 531             query.setBoolean("publish", true);
 532         }
 533     }
 534
 535
 536     /**
 537      * @param searchField the field in TaxonName to be searched through usually either <code>nameCache</code> or <code>titleCache</code>
 538      * @param queryString
 539      * @param classification TODO
 540      * @param matchMode
 541      * @param namedAreas
 542      * @param pageSize
 543      * @param pageNumber
 544      * @param doCount
 545      * @param clazz
 546      * @return
 547      *
 548      * FIXME implement classification restriction & implement test: see {@link TaxonDaoHibernateImplTest#testCountTaxaByName()}
 549      */
 550     private Query prepareTaxaByName(boolean doTaxa, boolean doSynonyms, boolean doMisappliedNames,
 551             boolean doCommonNames, boolean includeUnpublished, String searchField, String queryString,
 552             Classification classification, TaxonNode subtree, MatchMode matchMode, Set<NamedArea> namedAreas, NameSearchOrder order, Integer pageSize, Integer pageNumber, boolean doCount) {
 553
 554         Query query = prepareByNameQuery(doTaxa, doSynonyms, doMisappliedNames, doCommonNames, includeUnpublished,
 555                 searchField, queryString, classification, subtree, matchMode, namedAreas, order, doCount, false);
 556
 557         if(pageSize != null && !doCount && query != null) {
 558             query.setMaxResults(pageSize);
 559             if(pageNumber != null) {
 560                 query.setFirstResult(pageNumber * pageSize);
 561             }
 562         }
 563
 564         return query;
 565     }
 566
 567     private Query prepareTaxaByCommonName(String queryString, Classification classification,
 568             MatchMode matchMode, Set<NamedArea> namedAreas, Integer pageSize, Integer pageNumber,
 569             boolean doCount, boolean returnIdAndTitle){
 570
 571         String what = "select distinct";
 572         if (returnIdAndTitle){
 573                 what += " t.uuid, t.id, t.titleCache, \'taxon\', case when t.taxonNodes is empty and t.relationsFromThisTaxon is empty and t.relationsToThisTaxon is empty then true else false end ";
 574         }else {
 575                 what += (doCount ? " count(t)": " t");
 576         }
 577         String hql= what + " from Taxon t " +
 578         "join t.descriptions d "+
 579         "join d.descriptionElements e " +
 580         "join e.feature f " +
 581         "where f.supportsCommonTaxonName = true and e.name "+matchMode.getMatchOperator()+" :queryString";//and ls.text like 'common%'";
 582
 583         Query query = getSession().createQuery(hql);
 584
 585         query.setParameter("queryString", matchMode.queryStringFrom(queryString));
 586
 587         if(pageSize != null &&  !doCount) {
 588             query.setMaxResults(pageSize);
 589             if(pageNumber != null) {
 590                 query.setFirstResult(pageNumber * pageSize);
 591             }
 592         }
 593         return query;
 594     }
 595
 596     @Override
 597     public long countTaxaByName(boolean doTaxa, boolean doSynonyms, boolean doMisappliedNames, boolean doCommonNames,
 598             boolean doIncludeAuthors, String queryString, Classification classification, TaxonNode subtree,
 599         MatchMode matchMode, Set<NamedArea> namedAreas, boolean includeUnpublished) {
 600
 601         boolean doCount = true;
 602         /*
 603         boolean doTaxa = true;
 604         boolean doSynonyms = true;
 605         if (clazz.equals(Taxon.class)){
 606             doSynonyms = false;
 607         } else if (clazz.equals(Synonym.class)){
 608             doTaxa = false;
 609         }
 610         */
 611         String searchField = doIncludeAuthors ? "titleCache": "nameCache";
 612
 613         Query query = prepareTaxaByName(doTaxa, doSynonyms, doMisappliedNames, doCommonNames, includeUnpublished,
 614                 searchField, queryString, classification, subtree, matchMode, namedAreas, null, null, null, doCount);
 615         if (query != null) {
 616             return (Long)query.uniqueResult();
 617         }else{
 618             return 0;
 619         }
 620     }
 621
 622     /**
 623      * @param namedAreas
 624      * @param areasExpanded
 625      * @param areaQuery
 626      */
 627     private void expandNamedAreas(Collection<NamedArea> namedAreas, Set<NamedArea> areasExpanded, Query areaQuery) {
 628         for(NamedArea a : namedAreas){
 629             areasExpanded.add(a);
 630             areaQuery.setParameter("area", a);
 631             @SuppressWarnings("unchecked")
 632             List<NamedArea> childAreas = areaQuery.list();
 633             if(childAreas.size() > 0){
 634                 areasExpanded.addAll(childAreas);
 635                 expandNamedAreas(childAreas, areasExpanded, areaQuery);
 636             }
 637         }
 638     }
 639
 640
 641     @Override
 642     public UUID delete(TaxonBase taxonBase) throws DataAccessException{
 643         if (taxonBase == null){
 644             logger.warn("TaxonBase was 'null'");
 645             return null;
 646         }
 647
 648         // Merge the object in if it is detached
 649         //
 650         // I think this is preferable to catching lazy initialization errors
 651         // as that solution only swallows and hides the exception, but doesn't
 652         // actually solve it.
 653         taxonBase = (TaxonBase)getSession().merge(taxonBase);
 654
 655         taxonBase.removeSources();
 656
 657
 658         if (taxonBase instanceof Taxon){ // is Taxon
 659             Taxon taxon = ((Taxon)taxonBase);
 660             Set<Synonym> syns = new HashSet<>(taxon.getSynonyms());
 661             for (Synonym syn: syns){
 662                 taxon.removeSynonym(syn);
 663             }
 664         }
 665
 666        return super.delete(taxonBase);
 667
 668     }
 669
 670     @Override
 671     public List<TaxonBase> findByNameTitleCache(boolean doTaxa, boolean doSynonyms, boolean includeUnpublished, String queryString, Classification classification, TaxonNode subtree, MatchMode matchMode, Set<NamedArea> namedAreas, NameSearchOrder order, Integer pageNumber, Integer pageSize, List<String> propertyPaths) {
 672
 673         boolean doCount = false;
 674         Query query = prepareTaxaByName(doTaxa, doSynonyms, false, false, includeUnpublished, "titleCache", queryString, classification, subtree, matchMode, namedAreas, order, pageSize, pageNumber, doCount);
 675         if (query != null){
 676             @SuppressWarnings({ "unchecked", "rawtypes" })
 677             List<TaxonBase> results = query.list();
 678             defaultBeanInitializer.initializeAll(results, propertyPaths);
 679             return results;
 680         }
 681         return new ArrayList<>();
 682
 683     }
 684
 685     @Override
 686     public TaxonBase findByUuid(UUID uuid, List<Criterion> criteria, List<String> propertyPaths) {
 687
 688         Criteria crit = getSession().createCriteria(type);
 689
 690         if (uuid != null) {
 691             crit.add(Restrictions.eq("uuid", uuid));
 692         } else {
 693             logger.warn("UUID is NULL");
 694             return null;
 695         }
 696         if(criteria != null){
 697             for (Criterion criterion : criteria) {
 698                 crit.add(criterion);
 699             }
 700         }
 701         crit.addOrder(Order.asc("uuid"));
 702
 703         @SuppressWarnings({ "unchecked", "rawtypes" })
 704         List<? extends TaxonBase> results = crit.list();
 705         if (results.size() == 1) {
 706             defaultBeanInitializer.initializeAll(results, propertyPaths);
 707             TaxonBase<?> taxon = results.iterator().next();
 708             return taxon;
 709         } else if (results.size() > 1) {
 710             logger.error("Multiple results for UUID: " + uuid);
 711         } else if (results.size() == 0) {
 712             logger.info("No results for UUID: " + uuid);
 713         }
 714
 715         return null;
 716     }
 717
 718     @Override
 719     public List<? extends TaxonBase> findByUuids(List<UUID> uuids, List<Criterion> criteria, List<String> propertyPaths) {
 720
 721         Criteria crit = getSession().createCriteria(type);
 722
 723         if (uuids != null) {
 724             crit.add(Restrictions.in("uuid", uuids));
 725         } else {
 726             logger.warn("List<UUID> uuids is NULL");
 727             return null;
 728         }
 729         if(criteria != null){
 730             for (Criterion criterion : criteria) {
 731                 crit.add(criterion);
 732             }
 733         }
 734         crit.addOrder(Order.asc("uuid"));
 735
 736         @SuppressWarnings({ "unchecked", "rawtypes" })
 737         List<? extends TaxonBase> results = crit.list();
 738
 739         defaultBeanInitializer.initializeAll(results, propertyPaths);
 740         return results;
 741     }
 742
 743     @Override
 744     public long countMatchesByName(String queryString, MatchMode matchMode, boolean onlyAcccepted) {
 745         checkNotInPriorView("TaxonDaoHibernateImpl.countMatchesByName(String queryString, ITitledDao.MATCH_MODE matchMode, boolean onlyAcccepted)");
 746
 747         Criteria crit = getCriteria(type);
 748         crit.add(Restrictions.ilike("titleCache", matchMode.queryStringFrom(queryString)));
 749         crit.setProjection(Projections.rowCount());
 750         return (Long)crit.uniqueResult();
 751     }
 752
 753
 754     @Override
 755     public long countMatchesByName(String queryString, MatchMode matchMode, boolean onlyAcccepted, List<Criterion> criteria) {
 756         checkNotInPriorView("TaxonDaoHibernateImpl.countMatchesByName(String queryString, ITitledDao.MATCH_MODE matchMode, boolean onlyAcccepted, List<Criterion> criteria)");
 757
 758         Criteria crit = getCriteria(type);
 759         crit.add(Restrictions.ilike("titleCache", matchMode.queryStringFrom(queryString)));
 760         if(criteria != null){
 761             for (Criterion criterion : criteria) {
 762                 crit.add(criterion);
 763             }
 764         }
 765         crit.setProjection(Projections.rowCount());
 766         return (Long)crit.uniqueResult();
 767     }
 768
 769
 770     @Override
 771     public long countSynonyms(boolean onlyAttachedToTaxon) {
 772         AuditEvent auditEvent = getAuditEventFromContext();
 773         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
 774             Query query = null;
 775
 776             String queryStr =
 777                     " SELECT count(syn) "
 778                   + " FROM Synonym syn";
 779             if (onlyAttachedToTaxon){
 780                 queryStr += " WHERE syn.acceptedTaxon IS NOT NULL";
 781             }
 782             query = getSession().createQuery(queryStr);
 783
 784             return (Long)query.uniqueResult();
 785         } else {
 786             AuditQuery query = getAuditReader().createQuery().forEntitiesAtRevision(Synonym.class,auditEvent.getRevisionNumber());
 787             if (onlyAttachedToTaxon){
 788                 query.add(new NotNullAuditExpression(new EntityPropertyName("acceptedTaxon")));
 789             }
 790             query.addProjection(AuditEntity.id().count());
 791
 792             return (Long)query.getSingleResult();
 793         }
 794     }
 795
 796     @Override
 797     public long countSynonyms(Taxon taxon, SynonymType type) {
 798         AuditEvent auditEvent = getAuditEventFromContext();
 799         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
 800             Criteria criteria = getCriteria(Synonym.class);
 801
 802             criteria.add(Restrictions.eq("acceptedTaxon", taxon));
 803             if(type != null) {
 804                 criteria.add(Restrictions.eq("type", type));
 805             }
 806             criteria.setProjection(Projections.rowCount());
 807             return (Long)criteria.uniqueResult();
 808         } else {
 809             AuditQuery query = makeAuditQuery(Synonym.class,auditEvent);
 810             query.add(AuditEntity.relatedId("acceptedTaxon").eq(taxon.getId()));
 811             query.addProjection(AuditEntity.id().count());
 812
 813             if(type != null) {
 814                 query.add(AuditEntity.relatedId("type").eq(type.getId()));
 815             }
 816
 817             return (Long)query.getSingleResult();
 818         }
 819     }
 820
 821     @Override
 822     public long countSynonyms(Synonym synonym, SynonymType type) {
 823         AuditEvent auditEvent = getAuditEventFromContext();
 824         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
 825             Criteria criteria = getCriteria(Synonym.class);
 826
 827             criteria.add(Restrictions.isNotNull("acceptedTaxon"));
 828             if(type != null) {
 829                 criteria.add(Restrictions.eq("type", type));
 830             }
 831
 832             criteria.setProjection(Projections.rowCount());
 833             return (Long)criteria.uniqueResult();
 834         } else {
 835             AuditQuery query = makeAuditQuery(Synonym.class,auditEvent);
 836             query.add(new NotNullAuditExpression(new EntityPropertyName("acceptedTaxon")));
 837             query.addProjection(AuditEntity.id().count());
 838
 839             if(type != null) {
 840                 query.add(AuditEntity.relatedId("type").eq(type.getId()));
 841             }
 842
 843             return (Long)query.getSingleResult();
 844         }
 845     }
 846
 847     @Override
 848     public long countTaxaByName(Class<? extends TaxonBase> clazz, String genusOrUninomial, String infraGenericEpithet, String specificEpithet,  String infraSpecificEpithet, Rank rank) {
 849         checkNotInPriorView("TaxonDaoHibernateImpl.countTaxaByName(Boolean accepted, String genusOrUninomial,   String infraGenericEpithet, String specificEpithet,     String infraSpecificEpithet, Rank rank)");
 850         Criteria criteria = null;
 851
 852         criteria = getCriteria(clazz);
 853
 854         criteria.setFetchMode( "name", FetchMode.JOIN );
 855         criteria.createAlias("name", "name");
 856
 857         if(genusOrUninomial == null) {
 858             criteria.add(Restrictions.isNull("name.genusOrUninomial"));
 859         } else if(!genusOrUninomial.equals("*")) {
 860             criteria.add(Restrictions.eq("name.genusOrUninomial", genusOrUninomial));
 861         }
 862
 863         if(infraGenericEpithet == null) {
 864             criteria.add(Restrictions.isNull("name.infraGenericEpithet"));
 865         } else if(!infraGenericEpithet.equals("*")) {
 866             criteria.add(Restrictions.eq("name.infraGenericEpithet", infraGenericEpithet));
 867         }
 868
 869         if(specificEpithet == null) {
 870             criteria.add(Restrictions.isNull("name.specificEpithet"));
 871         } else if(!specificEpithet.equals("*")) {
 872             criteria.add(Restrictions.eq("name.specificEpithet", specificEpithet));
 873
 874         }
 875
 876         if(infraSpecificEpithet == null) {
 877             criteria.add(Restrictions.isNull("name.infraSpecificEpithet"));
 878         } else if(!infraSpecificEpithet.equals("*")) {
 879             criteria.add(Restrictions.eq("name.infraSpecificEpithet", infraSpecificEpithet));
 880         }
 881
 882         if(rank != null) {
 883             criteria.add(Restrictions.eq("name.rank", rank));
 884         }
 885
 886         criteria.setProjection(Projections.projectionList().add(Projections.rowCount()));
 887
 888         return (Long)criteria.uniqueResult();
 889     }
 890
 891     @Override
 892     public List<TaxonBase> findTaxaByName(Class<? extends TaxonBase> clazz, String genusOrUninomial, String infraGenericEpithet, String specificEpithet, String infraSpecificEpithet, String authorship, Rank rank, Integer pageSize,   Integer pageNumber) {
 893         checkNotInPriorView("TaxonDaoHibernateImpl.findTaxaByName(Boolean accepted, String genusOrUninomial, String infraGenericEpithet, String specificEpithet, String infraSpecificEpithet, String authorship, Rank rank, Integer pageSize,   Integer pageNumber)");
 894         Criteria criteria = getCriteria(clazz);
 895
 896         criteria.setFetchMode( "name", FetchMode.JOIN );
 897         criteria.createAlias("name", "name");
 898
 899         if(genusOrUninomial == null) {
 900             criteria.add(Restrictions.isNull("name.genusOrUninomial"));
 901         } else if(!genusOrUninomial.equals("*")) {
 902             criteria.add(Restrictions.eq("name.genusOrUninomial", genusOrUninomial));
 903         }
 904
 905         if(infraGenericEpithet == null) {
 906             criteria.add(Restrictions.isNull("name.infraGenericEpithet"));
 907         } else if(!infraGenericEpithet.equals("*")) {
 908             criteria.add(Restrictions.eq("name.infraGenericEpithet", infraGenericEpithet));
 909         }
 910
 911         if(specificEpithet == null) {
 912             criteria.add(Restrictions.isNull("name.specificEpithet"));
 913         } else if(!specificEpithet.equals("*")) {
 914             criteria.add(Restrictions.eq("name.specificEpithet", specificEpithet));
 915         }
 916
 917         if(infraSpecificEpithet == null) {
 918             criteria.add(Restrictions.isNull("name.infraSpecificEpithet"));
 919         } else if(!infraSpecificEpithet.equals("*")) {
 920             criteria.add(Restrictions.eq("name.infraSpecificEpithet", infraSpecificEpithet));
 921         }
 922
 923         if(authorship == null) {
 924             criteria.add(Restrictions.eq("name.authorshipCache", ""));
 925         } else if(!authorship.equals("*")) {
 926             criteria.add(Restrictions.eq("name.authorshipCache", authorship));
 927         }
 928
 929         if(rank != null) {
 930             criteria.add(Restrictions.eq("name.rank", rank));
 931         }
 932
 933         if(pageSize != null) {
 934             criteria.setMaxResults(pageSize);
 935             if(pageNumber != null) {
 936                 criteria.setFirstResult(pageNumber * pageSize);
 937             } else {
 938                 criteria.setFirstResult(0);
 939             }
 940         }
 941
 942         @SuppressWarnings({ "unchecked", "rawtypes" })
 943         List<TaxonBase> result = criteria.list();
 944         return result;
 945     }
 946
 947     @Override
 948     public long countTaxonRelationships(Taxon taxon, TaxonRelationshipType type,
 949             boolean includeUnpublished, Direction direction) {
 950         Set<TaxonRelationshipType> types = null;
 951         if (type != null){
 952             types = new HashSet<>();
 953             types.add(type);
 954         }
 955         return countTaxonRelationships(taxon, types, includeUnpublished, direction);
 956     }
 957
 958     @Override
 959     public long countTaxonRelationships(Taxon taxon, Set<TaxonRelationshipType> types,
 960             boolean includeUnpublished, Direction direction) {
 961         AuditEvent auditEvent = getAuditEventFromContext();
 962         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
 963
 964             String queryString = prepareTaxonRelationshipQuery(types, includeUnpublished, direction, true);
 965             Query query = getSession().createQuery(queryString);
 966             query.setParameter("relatedTaxon", taxon);
 967             if(types != null) {
 968                 query.setParameterList("types",types);
 969             }
 970             if(! includeUnpublished) {
 971                 query.setBoolean("publish",Boolean.TRUE);
 972             }
 973
 974
 975 //            if(type == null) {
 976 //                query = getSession().createQuery(
 977 //                        "select count(taxonRelationship) from TaxonRelationship taxonRelationship where taxonRelationship."+direction+" = :relatedTaxon");
 978 //            } else {
 979 //                query = getSession().createQuery("select count(taxonRelationship) from TaxonRelationship taxonRelationship where taxonRelationship."+direction+" = :relatedTaxon and taxonRelationship.type = :type");
 980 //                query.setParameter("type",type);
 981 //            }
 982 //            query.setParameter("relatedTaxon", taxon);
 983
 984             return (Long)query.uniqueResult();
 985         } else {
 986           //TODO unpublished
 987
 988             AuditQuery query = makeAuditQuery(TaxonRelationship.class, auditEvent);
 989             query.add(AuditEntity.relatedId(direction.toString()).eq(taxon.getId()));
 990             query.addProjection(AuditEntity.id().count());
 991
 992             if(types != null) {
 993                 //TODO adapt to new Set semantic, was single type before
 994 //                query.add(AuditEntity.relatedId("type").eq(type.getId()));
 995             }
 996
 997             return (Long)query.getSingleResult();
 998         }
 999     }
1000
1001
1002     /**
1003      * @param type
1004      * @param includeUnpublished
1005      * @param direction
1006      * @param b
1007      * @return
1008      */
1009     private String prepareTaxonRelationshipQuery(Set<TaxonRelationshipType> types, boolean includeUnpublished,
1010             Direction direction, boolean isCount) {
1011         String selectStr = isCount? " count(rel) as n ":" rel ";
1012         String result = "SELECT " + selectStr + " FROM TaxonRelationship rel ";
1013         if(direction != null){
1014             result += " WHERE rel."+direction+" = :relatedTaxon";
1015         } else {
1016             result += " WHERE (rel.relatedFrom = :relatedTaxon OR rel.relatedTo = :relatedTaxon )";
1017         }
1018         if (types != null){
1019             result += " AND rel.type IN (:types) ";
1020         }
1021         if(! includeUnpublished) {
1022             result += " AND rel."+direction.invers()+".publish = :publish";
1023         }
1024         return result;
1025     }
1026
1027     @Override
1028     public List<TaxonRelationship> getTaxonRelationships(Taxon taxon, TaxonRelationshipType type,
1029             boolean includeUnpublished, Integer pageSize, Integer pageNumber, List<OrderHint> orderHints,
1030             List<String> propertyPaths, Direction direction) {
1031         Set<TaxonRelationshipType> types = null;
1032         if (type != null){
1033             types = new HashSet<>();
1034             types.add(type);
1035         }
1036         return getTaxonRelationships(taxon, types, includeUnpublished, pageSize, pageNumber, orderHints, propertyPaths, direction);
1037     }
1038
1039     @Override
1040     public List<TaxonRelationship> getTaxonRelationships(Taxon taxon, Set<TaxonRelationshipType> types,
1041             boolean includeUnpublished, Integer pageSize, Integer pageNumber, List<OrderHint> orderHints,
1042             List<String> propertyPaths, Direction direction) {
1043
1044         AuditEvent auditEvent = getAuditEventFromContext();
1045         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
1046
1047             String queryString = prepareTaxonRelationshipQuery(types, includeUnpublished, direction, false);
1048
1049             queryString += orderByClause("rel", orderHints);
1050
1051             Query query = getSession().createQuery(queryString);
1052             query.setParameter("relatedTaxon", taxon);
1053             if(types != null) {
1054                 query.setParameterList("types",types);
1055             }
1056             if(! includeUnpublished) {
1057                 query.setBoolean("publish",Boolean.TRUE);
1058             }
1059             setPagingParameter(query, pageSize, pageNumber);
1060
1061 //            Criteria criteria = getSession().createCriteria(TaxonRelationship.class);
1062 //
1063 //            if(direction != null) {
1064 //                criteria.add(Restrictions.eq(direction.name(), taxon));
1065 //            } else {
1066 //                criteria.add(Restrictions.or(
1067 //                        Restrictions.eq(Direction.relatedFrom.name(), taxon),
1068 //                        Restrictions.eq(Direction.relatedTo.name(), taxon))
1069 //                    );
1070 //            }
1071 //
1072 //            if(type != null) {
1073 //                criteria.add(Restrictions.eq("type", type));
1074 //            }
1075 //
1076 //            addOrder(criteria,orderHints);
1077 //
1078 //            if(pageSize != null) {
1079 //                criteria.setMaxResults(pageSize);
1080 //                if(pageNumber != null) {
1081 //                    criteria.setFirstResult(pageNumber * pageSize);
1082 //                } else {
1083 //                    criteria.setFirstResult(0);
1084 //                }
1085 //            }
1086
1087             @SuppressWarnings("unchecked")
1088             List<TaxonRelationship> result = query.list();
1089             defaultBeanInitializer.initializeAll(result, propertyPaths);
1090
1091             return result;
1092         } else {
1093             //TODO unpublished
1094             AuditQuery query = getAuditReader().createQuery().forEntitiesAtRevision(TaxonRelationship.class,auditEvent.getRevisionNumber());
1095             query.add(AuditEntity.relatedId("relatedTo").eq(taxon.getId()));
1096
1097             if(types != null) {
1098                 //FIXME adapt to Set (was single type before)
1099 //                query.add(AuditEntity.relatedId("type").eq(types.getId()));
1100             }
1101
1102             if(pageSize != null) {
1103                 query.setMaxResults(pageSize);
1104                 if(pageNumber != null) {
1105                     query.setFirstResult(pageNumber * pageSize);
1106                 } else {
1107                     query.setFirstResult(0);
1108                 }
1109             }
1110
1111             @SuppressWarnings("unchecked")
1112             List<TaxonRelationship> result = query.getResultList();
1113             defaultBeanInitializer.initializeAll(result, propertyPaths);
1114
1115             // Ugly, but for now, there is no way to sort on a related entity property in Envers,
1116             // and we can't live without this functionality in CATE as it screws up the whole
1117             // taxon tree thing
1118             if(orderHints != null && !orderHints.isEmpty()) {
1119                 SortedSet<TaxonRelationship> sortedList = new TreeSet<>(new TaxonRelationshipFromTaxonComparator());
1120                 sortedList.addAll(result);
1121                 return new ArrayList<>(sortedList);
1122             }
1123
1124             return result;
1125         }
1126     }
1127
1128     class TaxonRelationshipFromTaxonComparator implements Comparator<TaxonRelationship> {
1129
1130         @Override
1131         public int compare(TaxonRelationship o1, TaxonRelationship o2) {
1132             if (o1.equals(o2)){
1133                 return 0;
1134             }
1135             int result = o1.getFromTaxon().getTitleCache().compareTo(o2.getFromTaxon().getTitleCache());
1136             if (result == 0 ){
1137                 result = o1.getUuid().compareTo(o2.getUuid());
1138             }
1139             return result;
1140         }
1141
1142     }
1143
1144     @Override
1145     public List<Synonym> getSynonyms(Taxon taxon, SynonymType type, Integer pageSize, Integer pageNumber, List<OrderHint> orderHints, List<String> propertyPaths) {
1146         AuditEvent auditEvent = getAuditEventFromContext();
1147         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
1148             Criteria criteria = getSession().createCriteria(Synonym.class);
1149
1150             criteria.add(Restrictions.eq("acceptedTaxon", taxon));
1151             if(type != null) {
1152                 criteria.add(Restrictions.eq("type", type));
1153             }
1154
1155             addOrder(criteria,orderHints);
1156
1157             if(pageSize != null) {
1158                 criteria.setMaxResults(pageSize);
1159                 if(pageNumber != null) {
1160                     criteria.setFirstResult(pageNumber * pageSize);
1161                 } else {
1162                     criteria.setFirstResult(0);
1163                 }
1164             }
1165
1166             @SuppressWarnings("unchecked")
1167             List<Synonym> result = criteria.list();
1168             defaultBeanInitializer.initializeAll(result, propertyPaths);
1169
1170             return result;
1171         } else {
1172             AuditQuery query = getAuditReader().createQuery().forEntitiesAtRevision(Synonym.class,auditEvent.getRevisionNumber());
1173             query.add(AuditEntity.relatedId("acceptedTaxon").eq(taxon.getId()));
1174
1175             if(type != null) {
1176                 query.add(AuditEntity.relatedId("type").eq(type.getId()));
1177             }
1178
1179             if(pageSize != null) {
1180                 query.setMaxResults(pageSize);
1181                 if(pageNumber != null) {
1182                     query.setFirstResult(pageNumber * pageSize);
1183                 } else {
1184                     query.setFirstResult(0);
1185                 }
1186             }
1187
1188             @SuppressWarnings("unchecked")
1189             List<Synonym> result = query.getResultList();
1190             defaultBeanInitializer.initializeAll(result, propertyPaths);
1191
1192             return result;
1193         }
1194     }
1195
1196     @Override
1197     public void rebuildIndex() {
1198         FullTextSession fullTextSession = Search.getFullTextSession(getSession());
1199
1200         for(TaxonBase<?> taxonBase : list(null,null)) { // re-index all taxon base
1201             Hibernate.initialize(taxonBase.getName());
1202             fullTextSession.index(taxonBase);
1203         }
1204         fullTextSession.flushToIndexes();
1205     }
1206
1207     @Override
1208     public String suggestQuery(String queryString) {
1209         throw new RuntimeException("Query suggestion currently not implemented in TaxonDaoHibernateImpl");
1210 //        checkNotInPriorView("TaxonDaoHibernateImpl.suggestQuery(String queryString)");
1211 //        String alternativeQueryString = null;
1212 //        if (alternativeSpellingSuggestionParser != null) {
1213 //            try {
1214 //
1215 //                alternativeSpellingSuggestionParser.parse(queryString);
1216 //                org.apache.lucene.search.Query alternativeQuery = alternativeSpellingSuggestionParser.suggest(queryString);
1217 //                if (alternativeQuery != null) {
1218 //                    alternativeQueryString = alternativeQuery
1219 //                            .toString("name.titleCache");
1220 //                }
1221 //
1222 //            } catch (ParseException e) {
1223 //                throw new QueryParseException(e, queryString);
1224 //            }
1225 //        }
1226 //        return alternativeQueryString;
1227     }
1228
1229     @Override
1230     public Taxon acceptedTaxonFor(Synonym synonym, Classification classificationFilter, List<String> propertyPaths){
1231
1232         String hql = prepareListAcceptedTaxaFor(classificationFilter, false);
1233
1234         Query query = getSession().createQuery(hql);
1235         query.setParameter("synonym", synonym);
1236         if(classificationFilter != null){
1237             query.setParameter("classificationFilter", classificationFilter);
1238         }
1239
1240         @SuppressWarnings("unchecked")
1241         List<Taxon> result = query.list();
1242
1243         defaultBeanInitializer.initializeAll(result, propertyPaths);
1244
1245         return result.isEmpty()? null: result.get(0);
1246     }
1247
1248     @Override
1249     public long countAcceptedTaxonFor(Synonym synonym, Classification classificationFilter){
1250
1251         String hql = prepareListAcceptedTaxaFor(classificationFilter, true);
1252
1253         Query query = getSession().createQuery(hql);
1254         query.setParameter("synonym", synonym);
1255         if(classificationFilter != null){
1256             query.setParameter("classificationFilter", classificationFilter);
1257         }
1258
1259         Long count = Long.parseLong(query.uniqueResult().toString());
1260         return count;
1261
1262     }
1263
1264
1265     /**
1266      * @param classificationFilter
1267      * @param orderHints
1268      * @return
1269      */
1270     private String prepareListAcceptedTaxaFor(Classification classificationFilter, boolean doCount) {
1271
1272         String hql;
1273         String hqlSelect = "SELECT " + (doCount? "COUNT(taxon)" : "taxon") +
1274                  " FROM Synonym as syn "
1275                  + "   JOIN syn.acceptedTaxon as taxon ";
1276         String hqlWhere = " WHERE syn = :synonym";
1277
1278         if(classificationFilter != null){
1279             hqlSelect += " JOIN taxon.taxonNodes AS taxonNode";
1280             hqlWhere  += " AND taxonNode.classification = :classificationFilter";
1281         }
1282         hql = hqlSelect + hqlWhere;
1283         return hql;
1284     }
1285
1286     @Override
1287     public TaxonBase find(LSID lsid) {
1288         TaxonBase<?> taxonBase = super.find(lsid);
1289         if(taxonBase != null) {
1290             List<String> propertyPaths = new ArrayList<>();
1291             propertyPaths.add("createdBy");
1292             propertyPaths.add("updatedBy");
1293             propertyPaths.add("name");
1294             propertyPaths.add("sec");
1295             propertyPaths.add("relationsToThisTaxon");
1296             propertyPaths.add("relationsToThisTaxon.fromTaxon");
1297             propertyPaths.add("relationsToThisTaxon.toTaxon");
1298             propertyPaths.add("relationsFromThisTaxon");
1299             propertyPaths.add("relationsFromThisTaxon.toTaxon");
1300             propertyPaths.add("relationsToThisTaxon.type");
1301             propertyPaths.add("synonyms");
1302             propertyPaths.add("synonyms.type");
1303             propertyPaths.add("descriptions");
1304
1305             defaultBeanInitializer.initialize(taxonBase, propertyPaths);
1306         }
1307         return taxonBase;
1308     }
1309
1310     @Override
1311     public List<String> taxaByNameNotInDB(List<String> taxonNames){
1312         //get all taxa, already in db
1313         Query query = getSession().createQuery(
1314                  " FROM TaxonName t "
1315                 +" WHERE t.nameCache IN (:taxonList)");
1316         query.setParameterList("taxonList", taxonNames);
1317         @SuppressWarnings("unchecked")
1318         List<TaxonName> taxaInDB = query.list();
1319         //compare the original list with the result of the query
1320         for (TaxonName taxonName: taxaInDB){
1321             String nameCache = taxonName.getNameCache();
1322             if (taxonNames.contains(nameCache)){
1323                 taxonNames.remove(nameCache);
1324             }
1325         }
1326
1327         return taxonNames;
1328     }
1329
1330     //TODO: mal nur mit UUID probieren (ohne fetch all properties), vielleicht geht das schneller?
1331     @Override
1332     public List<UUID> findIdenticalTaxonNameIds(List<String> propertyPaths){
1333         Query query=getSession().createQuery(
1334                    "SELECT tmb2 "
1335                 + " FROM ZoologicalName tmb, ZoologicalName tmb2 FETCH ALL properties "
1336                 + " WHERE tmb.id != tmb2.id AND tmb.nameCache = tmb2.nameCache");
1337         @SuppressWarnings("unchecked")
1338         List<UUID> zooNames = query.list();
1339
1340         return zooNames;
1341
1342     }
1343
1344     @Override
1345     public List<TaxonName> findIdenticalTaxonNames(List<String> propertyPaths) {
1346
1347         Query query=getSession().createQuery(
1348                   " SELECT tmb2 "
1349                 + " FROM ZoologicalName tmb, ZoologicalName tmb2 FETCH ALL properties "
1350                 + " WHERE tmb.id != tmb2.id AND tmb.nameCache = tmb2.nameCache");
1351
1352         @SuppressWarnings("unchecked")
1353         List<TaxonName> zooNames = query.list();
1354
1355         TaxonNameComparator taxComp = new TaxonNameComparator();
1356         Collections.sort(zooNames, taxComp);
1357
1358         for (TaxonName taxonName: zooNames){
1359             defaultBeanInitializer.initialize(taxonName, propertyPaths);
1360         }
1361
1362         return zooNames;
1363     }
1364
1365     @Override
1366     public List<TaxonName> findIdenticalNamesNew(List<String> propertyPaths){
1367
1368         //Hole die beiden Source_ids von "Fauna Europaea" und "Erms" und in sources der names darf jeweils nur das entgegengesetzte auftreten (i member of tmb.taxonBases)
1369         Query query = getSession().createQuery("SELECT id "
1370                 + "FROM Reference "
1371                 + " WHERE titleCache LIKE 'Fauna Europaea database'");
1372         @SuppressWarnings("unchecked")
1373         List<String> secRefFauna = query.list();
1374         query = getSession().createQuery("Select id from Reference where titleCache like 'ERMS'");
1375         @SuppressWarnings("unchecked")
1376         List<String> secRefErms = query.list();
1377         //Query query = getSession().createQuery("select tmb2.nameCache from ZoologicalName tmb, TaxonBase tb1, ZoologicalName tmb2, TaxonBase tb2 where tmb.id != tmb2.id and tb1.name = tmb and tb2.name = tmb2 and tmb.nameCache = tmb2.nameCache and tb1.sec != tb2.sec");
1378         //Get all names of fauna europaea
1379         query = getSession().createQuery("select zn.nameCache from ZoologicalName zn, TaxonBase tb where tb.name = zn and tb.sec.id = :secRefFauna");
1380         query.setParameter("secRefFauna", secRefFauna.get(0));
1381         @SuppressWarnings("unchecked")
1382         List<String> namesFauna= query.list();
1383
1384         //Get all names of erms
1385
1386         query = getSession().createQuery("select zn.nameCache from ZoologicalName zn, TaxonBase tb where tb.name = zn and tb.sec.id = :secRefErms");
1387         query.setParameter("secRefErms", secRefErms.get(0));
1388
1389         @SuppressWarnings("unchecked")
1390         List<String> namesErms = query.list();
1391         /*TaxonNameComparator comp = new TaxonNameComparator();
1392         Collections.sort(namesFauna);
1393         Collections.sort(namesErms);
1394         */
1395         List <String> identicalNames = new ArrayList<>();
1396
1397         for (String nameFauna: namesFauna){
1398             if (namesErms.contains(nameFauna)){
1399                 identicalNames.add(nameFauna);
1400             }
1401         }
1402
1403         query = getSession().createQuery("from ZoologicalName zn where zn.nameCache IN (:identicalNames)");
1404         query.setParameterList("identicalNames", identicalNames);
1405         List<TaxonName> result = query.list();
1406         TaxonName tempName = result.get(0);
1407
1408         Iterator<IdentifiableSource> sources = tempName.getSources().iterator();
1409
1410         TaxonNameComparator taxComp = new TaxonNameComparator();
1411         Collections.sort(result, taxComp);
1412         defaultBeanInitializer.initializeAll(result, propertyPaths);
1413         return result;
1414
1415     }
1416
1417 //
1418 //
1419 //    @Override
1420 //    public String getPhylumName(TaxonName name){
1421 //        List results = new ArrayList();
1422 //        try{
1423 //        Query query = getSession().createSQLQuery("select getPhylum("+ name.getId()+");");
1424 //        results = query.list();
1425 //        }catch(Exception e){
1426 //            System.err.println(name.getUuid());
1427 //            return null;
1428 //        }
1429 //        System.err.println("phylum of "+ name.getTitleCache() );
1430 //        return (String)results.get(0);
1431 //    }
1432
1433
1434     @Override
1435     public long countTaxaByCommonName(String searchString,
1436             Classification classification, MatchMode matchMode,
1437             Set<NamedArea> namedAreas) {
1438         boolean doCount = true;
1439         Query query = prepareTaxaByCommonName(searchString, classification, matchMode, namedAreas, null, null, doCount, false);
1440         if (query != null && !query.list().isEmpty()) {
1441             Object o = query.uniqueResult();
1442             if(o != null) {
1443                 return (Long)o;
1444             }
1445         }
1446         return 0;
1447     }
1448
1449     private Subselects createByNameHQLString(boolean doConceptRelations,
1450                 boolean includeUnpublished, Classification classification, TaxonNode subtree,
1451                 Set<NamedArea> areasExpanded, MatchMode matchMode, String searchField){
1452
1453
1454         boolean doAreaRestriction = areasExpanded.size() > 0;
1455         boolean hasTaxonNodeFilter = classification != null || subtree != null;
1456
1457         String doAreaRestrictionSubSelect =
1458                      " SELECT %s.id "
1459                    + " FROM Distribution e "
1460                    + "    JOIN e.inDescription d "
1461                    + "    JOIN d.taxon t " +
1462                 (hasTaxonNodeFilter ? " JOIN t.taxonNodes AS tn " : " ");
1463
1464         String doAreaRestrictionConceptRelationSubSelect =
1465                    "SELECT %s.id "
1466                    + " FROM Distribution e "
1467                    + "   JOIN e.inDescription d"
1468                    + "   JOIN d.taxon t";
1469
1470         String doTaxonSubSelect =
1471                      " SELECT %s.id "
1472                    + " FROM Taxon t " + (hasTaxonNodeFilter ? " "
1473                            + " JOIN t.taxonNodes AS tn " : " ");
1474
1475         String doTaxonMisappliedNameSubSelect =
1476                      " SELECT %s.id "
1477                    + " FROM Taxon t ";
1478
1479         String doTaxonNameJoin = " JOIN t.name n ";
1480
1481         String doSynonymNameJoin =
1482                    " JOIN t.synonyms s "
1483                  + " JOIN s.name sn";
1484
1485         String doConceptRelationJoin =
1486                    " LEFT JOIN t.relationsFromThisTaxon AS rft " +
1487                    " LEFT JOIN rft.relatedTo AS rt " +
1488                       (hasTaxonNodeFilter ? " LEFT JOIN rt.taxonNodes AS tn2 " : " ") +
1489                    " LEFT JOIN rt.name AS n2" +
1490                    " LEFT JOIN rft.type as rtype";
1491
1492         String doCommonNamesJoin =
1493                    " JOIN t.descriptions AS description "+
1494                    " LEFT JOIN description.descriptionElements AS com " +
1495                    " LEFT JOIN com.feature f ";
1496
1497
1498         String doTreeWhere = classification == null ? "" : " AND tn.classification = :classification";
1499         String doTreeForConceptRelationsWhere = classification == null ? "": " AND tn2.classification = :classification";
1500
1501         String doSubtreeWhere = subtree == null? "":" AND tn.treeIndex like :treeIndexLike";
1502         String doSubtreeForConceptRelationsWhere = subtree == null? "":" AND tn2.treeIndex like :treeIndexLike";
1503
1504         String doAreaRestrictionWhere =  " e.area.uuid in (:namedAreasUuids)";
1505         String doCommonNamesRestrictionWhere = " (f.supportsCommonTaxonName = true and com.name "+matchMode.getMatchOperator()+" :queryString )";
1506
1507         String doSearchFieldWhere = "%s." + searchField + " " + matchMode.getMatchOperator() + " :queryString";
1508
1509         String doRelationshipTypeComparison = " rtype in (:rTypeSet) ";
1510
1511         String taxonSubselect = null;
1512         String synonymSubselect = null;
1513         String conceptSelect = null;
1514         String commonNameSubselect = null;
1515
1516         if(hasTaxonNodeFilter){
1517             if (!doConceptRelations){
1518                 if(doAreaRestriction){
1519                     taxonSubselect = String.format(doAreaRestrictionSubSelect, "t") + doTaxonNameJoin +
1520                             " WHERE (1=1) AND " + doAreaRestrictionWhere +
1521                                 doTreeWhere + doSubtreeWhere +
1522                             "  AND " + String.format(doSearchFieldWhere, "n");
1523                     synonymSubselect = String.format(doAreaRestrictionSubSelect, "s") + doSynonymNameJoin +
1524                             " WHERE (1=1) AND " + doAreaRestrictionWhere +
1525                                 doTreeWhere + doSubtreeWhere +
1526                             "  AND " + String.format(doSearchFieldWhere, "sn");
1527                     commonNameSubselect =  String.format(doAreaRestrictionSubSelect, "t") + doCommonNamesJoin +
1528                             " WHERE (1=1) AND " +  doAreaRestrictionWhere +
1529                                  doTreeWhere + doSubtreeWhere +
1530                             "  AND " + String.format(doSearchFieldWhere, "n") +
1531                             "  AND " + doCommonNamesRestrictionWhere;
1532                 } else {//no area restriction
1533                     taxonSubselect = String.format(doTaxonSubSelect, "t" )+ doTaxonNameJoin +
1534                             " WHERE (1=1) " + doTreeWhere + doSubtreeWhere +
1535                             "  AND " + String.format(doSearchFieldWhere, "n");
1536                     synonymSubselect = String.format(doTaxonSubSelect, "s" ) + doSynonymNameJoin +
1537                             " WHERE (1=1) " + doTreeWhere + doSubtreeWhere +
1538                             "  AND " + String.format(doSearchFieldWhere, "sn");
1539                     commonNameSubselect =String.format(doTaxonSubSelect, "t" )+ doCommonNamesJoin +
1540                             " WHERE (1=1) " + doTreeWhere + doSubtreeWhere +
1541                             "  AND " + doCommonNamesRestrictionWhere;
1542                 }
1543             }else{ //concept relations included
1544                 if(doAreaRestriction){
1545                     conceptSelect = String.format(doAreaRestrictionConceptRelationSubSelect, "t") + doTaxonNameJoin + doConceptRelationJoin  +
1546                             " WHERE " + doAreaRestrictionWhere +
1547                             "  AND " + String.format(doSearchFieldWhere, "n") +
1548                                  doTreeForConceptRelationsWhere + doSubtreeForConceptRelationsWhere +
1549                             "  AND " + doRelationshipTypeComparison;
1550                     taxonSubselect = String.format(doAreaRestrictionSubSelect, "t") + doTaxonNameJoin +
1551                             " WHERE " + doAreaRestrictionWhere +
1552                             "  AND " + String.format(doSearchFieldWhere, "n") +
1553                                 doTreeWhere + doSubtreeWhere;
1554                     synonymSubselect = String.format(doAreaRestrictionSubSelect, "s") + doSynonymNameJoin +
1555                             " WHERE " + doAreaRestrictionWhere +
1556                                 doTreeWhere + doSubtreeWhere +
1557                             "  AND " + String.format(doSearchFieldWhere, "sn");
1558                     commonNameSubselect= String.format(doAreaRestrictionSubSelect, "t")+ doCommonNamesJoin +
1559                             " WHERE " + doAreaRestrictionWhere +
1560                                 doTreeWhere + doSubtreeWhere +
1561                             "  AND " + doCommonNamesRestrictionWhere;
1562                 } else {//no area restriction
1563                     conceptSelect = String.format(doTaxonMisappliedNameSubSelect, "t" ) + doTaxonNameJoin + doConceptRelationJoin +
1564                             " WHERE " + String.format(doSearchFieldWhere, "n") +
1565                                   doTreeForConceptRelationsWhere + doSubtreeForConceptRelationsWhere +
1566                             "  AND " + doRelationshipTypeComparison;
1567                     taxonSubselect = String.format(doTaxonSubSelect, "t" ) + doTaxonNameJoin +
1568                             " WHERE " +  String.format(doSearchFieldWhere, "n") +
1569                                  doTreeWhere + doSubtreeWhere;
1570                     synonymSubselect = String.format(doTaxonSubSelect, "s" ) + doSynonymNameJoin +
1571                             " WHERE (1=1) " + doTreeWhere + doSubtreeWhere +
1572                             "  AND " +  String.format(doSearchFieldWhere, "sn");
1573                     commonNameSubselect= String.format(doTaxonSubSelect, "t")+ doCommonNamesJoin +
1574                             " WHERE (1=1) " + doTreeWhere + doSubtreeWhere +
1575                             "  AND " + doCommonNamesRestrictionWhere;
1576                 }
1577             }
1578         } else { //classification = null && subtree = null
1579             if(doAreaRestriction){
1580                 conceptSelect = String.format(doAreaRestrictionConceptRelationSubSelect, "t") + doTaxonNameJoin + doConceptRelationJoin +
1581                         " WHERE " + doAreaRestrictionWhere +
1582                         "  AND " + String.format(doSearchFieldWhere, "n")+
1583                         "  AND " + doRelationshipTypeComparison;
1584                 taxonSubselect = String.format(doAreaRestrictionSubSelect, "t") + doTaxonNameJoin +
1585                         " WHERE " + doAreaRestrictionWhere +
1586                         "  AND " + String.format(doSearchFieldWhere, "n");
1587                 synonymSubselect = String.format(doAreaRestrictionSubSelect, "s") + doSynonymNameJoin +
1588                         " WHERE " + doAreaRestrictionWhere +
1589                         "  AND " + String.format(doSearchFieldWhere, "sn");
1590                 commonNameSubselect = String.format(doAreaRestrictionSubSelect, "t")+ doCommonNamesJoin +
1591                         " WHERE " + doAreaRestrictionWhere +
1592                         "  AND " + doCommonNamesRestrictionWhere;
1593             } else { //no area restriction
1594                 conceptSelect = String.format(doTaxonMisappliedNameSubSelect, "t" ) + doTaxonNameJoin + doConceptRelationJoin +
1595                         " WHERE " +  String.format(doSearchFieldWhere, "n") +
1596                         " AND " + doRelationshipTypeComparison;
1597                 taxonSubselect = String.format(doTaxonSubSelect, "t" ) + doTaxonNameJoin +
1598                         " WHERE " +  String.format(doSearchFieldWhere, "n");
1599                 synonymSubselect = String.format(doTaxonSubSelect, "s" ) + doSynonymNameJoin +
1600                         " WHERE " +  String.format(doSearchFieldWhere, "sn");
1601                 commonNameSubselect = String.format(doTaxonSubSelect, "t" ) +doCommonNamesJoin +
1602                         " WHERE "+  doCommonNamesRestrictionWhere;
1603             }
1604         }
1605
1606         if (!includeUnpublished){
1607             taxonSubselect   += " AND t.publish = :publish ";
1608             synonymSubselect += " AND s.publish = :publish AND t.publish = :publish ";
1609             commonNameSubselect += " AND t.publish = :publish ";
1610             conceptSelect += " AND t.publish = :publish AND rt.publish = :publish ";
1611         }
1612
1613         Subselects result = new Subselects(taxonSubselect, synonymSubselect, conceptSelect, commonNameSubselect);
1614         return result;
1615     }
1616
1617     private class Subselects{
1618         String taxonSubselect;
1619         String synonymSubselect;
1620         String conceptSelect;
1621         String commonNameSubselect;
1622         private Subselects(String taxonSubselect, String synonymSubselect, String conceptSelect,
1623                 String commonNameSubselect) {
1624             this.taxonSubselect = taxonSubselect;
1625             this.synonymSubselect = synonymSubselect;
1626             this.conceptSelect = conceptSelect;
1627             this.commonNameSubselect = commonNameSubselect;
1628         }
1629     }
1630
1631         @Override
1632         public List<UuidAndTitleCache<Taxon>> getTaxaByCommonNameForEditor(
1633                         String titleSearchStringSqlized, Classification classification,
1634                         MatchMode matchMode, Set<NamedArea> namedAreas) {
1635                 Query query = prepareTaxaByCommonName(titleSearchStringSqlized, classification, matchMode, namedAreas, null, null, false, true);
1636         if (query != null){
1637             @SuppressWarnings("unchecked")
1638             List<Object> resultArray = query.list();
1639             List<UuidAndTitleCache<Taxon>> returnResult = new ArrayList<>() ;
1640             Object[] result;
1641             for(int i = 0; i<resultArray.size();i++){
1642                 result = (Object[]) resultArray.get(i);
1643                 returnResult.add(new UuidAndTitleCache<Taxon>(Taxon.class, (UUID) result[0],(Integer)result[1], (String)result[2], new Boolean(result[4].toString()), null));
1644             }
1645             return returnResult;
1646         }else{
1647             return new ArrayList<>();
1648         }
1649         }
1650
1651
1652         /**
1653          * @param
1654          * @see eu.etaxonomy.cdm.persistence.dao.taxon.ITaxonDao#countByIdentifier(java.lang.Class, java.lang.String, eu.etaxonomy.cdm.model.common.DefinedTerm, eu.etaxonomy.cdm.model.taxon.TaxonNode, eu.etaxonomy.cdm.persistence.query.MatchMode)
1655          */
1656         @Override
1657         public <S extends TaxonBase> long countByIdentifier(Class<S> clazz,
1658                         String identifier, DefinedTerm identifierType, TaxonNode subtreeFilter, MatchMode matchmode) {
1659                 if (subtreeFilter == null){
1660                         return countByIdentifier(clazz, identifier, identifierType, matchmode);
1661                 }
1662
1663                 Class<?> clazzParam = (clazz == null) ? type : clazz;
1664                 checkNotInPriorView("TaxonDaoHibernateImpl.countByIdentifier(T clazz, String identifier, DefinedTerm identifierType, TaxonNode subMatchMode matchmode)");
1665
1666                 boolean isTaxon = clazzParam == Taxon.class || clazzParam == TaxonBase.class;
1667                 boolean isSynonym = clazzParam == Synonym.class || clazzParam == TaxonBase.class;
1668
1669                 getSession().update(subtreeFilter);  //to avoid LIE when retrieving treeindex
1670                 String filterStr = "'" + subtreeFilter.treeIndex() + "%%'";
1671                 String accTreeJoin = isTaxon? " LEFT JOIN c.taxonNodes tn  " : "";
1672                 String synTreeJoin = isSynonym ? " LEFT JOIN c.acceptedTaxon as acc LEFT JOIN acc.taxonNodes synTn  " : "";
1673                 String accWhere = isTaxon ?  "tn.treeIndex like " + filterStr : "(1=0)";
1674                 String synWhere = isSynonym  ?  "synTn.treeIndex like " + filterStr : "(1=0)";
1675
1676                 String queryString = "SELECT count(*)  FROM %s as c " +
1677                 " INNER JOIN c.identifiers as ids " +
1678                 accTreeJoin +
1679                 synTreeJoin +
1680                 " WHERE (1=1) " +
1681                         "  AND ( " + accWhere + " OR " + synWhere + ")";
1682                 queryString = String.format(queryString, clazzParam.getSimpleName());
1683
1684                 if (identifier != null){
1685                         if (matchmode == null || matchmode == MatchMode.EXACT){
1686                                 queryString += " AND ids.identifier = '"  + identifier + "'";
1687                         }else {
1688                                 queryString += " AND ids.identifier LIKE '" + matchmode.queryStringFrom(identifier)  + "'";
1689                         }
1690                 }
1691                 if (identifierType != null){
1692                 queryString += " AND ids.type = :type";
1693         }
1694
1695                 Query query = getSession().createQuery(queryString);
1696         if (identifierType != null){
1697                 query.setEntity("type", identifierType);
1698         }
1699
1700                 return (Long)query.uniqueResult();
1701         }
1702
1703         @Override
1704         public <S extends TaxonBase> List<Object[]> findByIdentifier(
1705                         Class<S> clazz, String identifier, DefinedTerm identifierType, TaxonNode subtreeFilter,
1706                         MatchMode matchmode, boolean includeEntity,
1707                         Integer pageSize, Integer pageNumber, List<String> propertyPaths) {
1708
1709                 checkNotInPriorView("TaxonDaoHibernateImpl.findByIdentifier(T clazz, String identifier, DefinedTerm identifierType, MatchMode matchmode, Integer pageSize, Integer pageNumber, List<OrderHint> orderHints, List<String> propertyPaths)");
1710                 Class<?> clazzParam = clazz == null ? type : clazz;
1711
1712                 boolean isTaxon = clazzParam == Taxon.class || clazzParam == TaxonBase.class;
1713                 boolean isSynonym = clazzParam == Synonym.class || clazzParam == TaxonBase.class;
1714                 getSession().update(subtreeFilter);  //to avoid LIE when retrieving treeindex
1715                 String filterStr = "'" + subtreeFilter.treeIndex() + "%%'";
1716                 String accTreeJoin = isTaxon? " LEFT JOIN c.taxonNodes tn  " : "";
1717                 String synTreeJoin = isSynonym ? " LEFT JOIN c.acceptedTaxon as acc LEFT JOIN acc.taxonNodes synTn  " : "";
1718                 String accWhere = isTaxon ?  "tn.treeIndex like " + filterStr : "(1=0)";
1719                 String synWhere = isSynonym  ?  "synTn.treeIndex like " + filterStr : "(1=0)";
1720
1721                 String queryString = " SELECT ids.type, ids.identifier, %s " +
1722                                 " FROM %s as c " +
1723                 " INNER JOIN c.identifiers as ids " +
1724                 accTreeJoin +
1725                                 synTreeJoin +
1726                 " WHERE (1=1) " +
1727                         " AND ( " + accWhere + " OR " + synWhere + ")";
1728                 queryString = String.format(queryString, (includeEntity ? "c":"c.uuid, c.titleCache") , clazzParam.getSimpleName());
1729
1730                 //Matchmode and identifier
1731                 if (identifier != null){
1732                         if (matchmode == null || matchmode == MatchMode.EXACT){
1733                                 queryString += " AND ids.identifier = '"  + identifier + "'";
1734                         }else {
1735                                 queryString += " AND ids.identifier LIKE '" + matchmode.queryStringFrom(identifier)  + "'";
1736                         }
1737                 }
1738         if (identifierType != null){
1739                 queryString += " AND ids.type = :type";
1740         }
1741         //order
1742         queryString +=" ORDER BY ids.type.uuid, ids.identifier, c.uuid ";
1743
1744                 Query query = getSession().createQuery(queryString);
1745
1746                 //parameters
1747                 if (identifierType != null){
1748                 query.setEntity("type", identifierType);
1749         }
1750
1751         //paging
1752         setPagingParameter(query, pageSize, pageNumber);
1753
1754         List<Object[]> results = query.list();
1755         //initialize
1756         if (includeEntity){
1757                 List<S> entities = new ArrayList<S>();
1758                 for (Object[] result : results){
1759                         entities.add((S)result[2]);
1760                 }
1761                 defaultBeanInitializer.initializeAll(entities, propertyPaths);
1762         }
1763         return results;
1764         }
1765
1766     /**
1767      * {@inheritDoc}
1768      * @see #countByIdentifier(Class, String, DefinedTerm, TaxonNode, MatchMode)
1769      */
1770     @Override
1771     public <S extends TaxonBase> long countByMarker(Class<S> clazz, MarkerType markerType,
1772             Boolean markerValue, TaxonNode subtreeFilter) {
1773         if (markerType == null){
1774             return 0;
1775         }
1776
1777         if (subtreeFilter == null){
1778             return countByMarker(clazz, markerType, markerValue);
1779         }
1780
1781         Class<?> clazzParam = clazz == null ? type : clazz;
1782         checkNotInPriorView("TaxonDaoHibernateImpl.countByMarker(Class<S> clazz, DefinedTerm markerType, boolean markerValue, TaxonNode subtreeFilter)");
1783
1784         boolean isTaxon = clazzParam == Taxon.class || clazzParam == TaxonBase.class;
1785         boolean isSynonym = clazzParam == Synonym.class || clazzParam == TaxonBase.class;
1786
1787         getSession().update(subtreeFilter);  //to avoid LIE when retrieving treeindex
1788         String filterStr = "'" + subtreeFilter.treeIndex() + "%%'";
1789         String accTreeJoin = isTaxon? " LEFT JOIN c.taxonNodes tn  " : "";
1790         String synTreeJoin = isSynonym ? " LEFT JOIN c.acceptedTaxon acc LEFT JOIN acc.taxonNodes synTn  " : "";
1791         String accWhere = isTaxon ?  "tn.treeIndex like " + filterStr : "(1=0)";
1792         String synWhere = isSynonym  ?  "synTn.treeIndex like " + filterStr : "(1=0)";
1793
1794         String queryString = "SELECT count(*)  FROM %s as c " +
1795                 " INNER JOIN c.markers as mks " +
1796                 accTreeJoin +
1797                 synTreeJoin +
1798                 " WHERE (1=1) " +
1799                     "  AND ( " + accWhere + " OR " + synWhere + ")";
1800         queryString = String.format(queryString, clazzParam.getSimpleName());
1801
1802         if (markerValue != null){
1803             queryString += " AND mks.flag = :flag";
1804         }
1805         if (markerType != null){
1806             queryString += " AND mks.markerType = :type";
1807         }
1808
1809         Query query = getSession().createQuery(queryString);
1810         if (markerType != null){
1811             query.setEntity("type", markerType);
1812         }
1813         if (markerValue != null){
1814             query.setBoolean("flag", markerValue);
1815         }
1816
1817         Long c = (Long)query.uniqueResult();
1818         return c;
1819     }
1820
1821     /**
1822      * {@inheritDoc}
1823      */
1824     @Override
1825     public <S extends TaxonBase> List<Object[]> findByMarker(Class<S> clazz, MarkerType markerType,
1826             Boolean markerValue, TaxonNode subtreeFilter, boolean includeEntity,
1827             TaxonTitleType titleType, Integer pageSize, Integer pageNumber, List<String> propertyPaths) {
1828         checkNotInPriorView("TaxonDaoHibernateImpl.findByMarker(T clazz, String identifier, DefinedTerm identifierType, MatchMode matchmode, Integer pageSize, Integer pageNumber, List<OrderHint> orderHints, List<String> propertyPaths)");
1829         if (markerType == null){
1830             return new ArrayList<Object[]>();
1831         }
1832         if (titleType == null){
1833             titleType = TaxonTitleType.DEFAULT();
1834         }
1835
1836         Class<?> clazzParam = clazz == null ? type : clazz;
1837
1838         boolean isTaxon = clazzParam == Taxon.class || clazzParam == TaxonBase.class;
1839         boolean isSynonym = clazzParam == Synonym.class || clazzParam == TaxonBase.class;
1840         getSession().update(subtreeFilter);  //to avoid LIE when retrieving treeindex
1841         String filterStr = "'" + subtreeFilter.treeIndex() + "%%'";
1842         String accTreeJoin = isTaxon? " LEFT JOIN c.taxonNodes tn  " : "";
1843         String synTreeJoin = isSynonym ? " LEFT JOIN c.acceptedTaxon as acc LEFT JOIN acc.taxonNodes synTn  " : "";
1844         String accWhere = isTaxon ?  "tn.treeIndex like " + filterStr : "(1=0)";
1845         String synWhere = isSynonym  ?  "synTn.treeIndex like " + filterStr : "(1=0)";
1846         String selectParams = includeEntity ? "c" : titleType.hqlReplaceSelect("c.uuid, c.titleCache", "c.titleCache");
1847         String titleTypeJoin = includeEntity ? "" : titleType.hqlJoin();
1848
1849         String queryString = "SELECT mks.markerType, mks.flag, %s " +
1850                 " FROM %s as c " +
1851                 " INNER JOIN c.markers as mks " +
1852                 titleTypeJoin +
1853                 accTreeJoin +
1854                 synTreeJoin +
1855                 " WHERE (1=1) " +
1856                     " AND ( " + accWhere + " OR " + synWhere + ")";
1857         queryString = String.format(queryString, selectParams, clazzParam.getSimpleName());
1858
1859         //type and value
1860         if (markerValue != null){
1861             queryString += " AND mks.flag = :flag";
1862         }
1863         queryString += " AND mks.markerType = :type";
1864         //order
1865         queryString +=" ORDER BY mks.markerType.uuid, mks.flag, c.uuid ";
1866
1867         Query query = getSession().createQuery(queryString);
1868
1869         //parameters
1870         query.setEntity("type", markerType);
1871         if (markerValue != null){
1872             query.setBoolean("flag", markerValue);
1873         }
1874
1875         //paging
1876         setPagingParameter(query, pageSize, pageNumber);
1877
1878         List<Object[]> results = query.list();
1879         //initialize
1880         if (includeEntity){
1881             List<S> entities = new ArrayList<S>();
1882             for (Object[] result : results){
1883                 entities.add((S)result[2]);
1884             }
1885             defaultBeanInitializer.initializeAll(entities, propertyPaths);
1886         }
1887         return results;
1888     }
1889
1890     @Override
1891     public long countTaxonRelationships(Set<TaxonRelationshipType> types) {
1892         Criteria criteria = getSession().createCriteria(TaxonRelationship.class);
1893
1894         if (types != null) {
1895             if (types.isEmpty()){
1896                 return 0l;
1897             }else{
1898                 criteria.add(Restrictions.in("type", types) );
1899             }
1900         }
1901         //count
1902         criteria.setProjection(Projections.rowCount());
1903         long result = (Long)criteria.uniqueResult();
1904
1905         return result;
1906     }
1907
1908     @Override
1909     public List<TaxonRelationship> getTaxonRelationships(Set<TaxonRelationshipType> types,
1910             Integer pageSize, Integer pageNumber,
1911             List<OrderHint> orderHints, List<String> propertyPaths) {
1912         Criteria criteria = getCriteria(TaxonRelationship.class);
1913
1914         if (types != null) {
1915             if (types.isEmpty()){
1916                 return new ArrayList<>();
1917             }else{
1918                 criteria.add(Restrictions.in("type", types) );
1919             }
1920         }
1921         addOrder(criteria,orderHints);
1922         addPageSizeAndNumber(criteria, pageSize, pageNumber);
1923
1924         @SuppressWarnings("unchecked")
1925         List<TaxonRelationship> results = criteria.list();
1926         defaultBeanInitializer.initializeAll(results, propertyPaths);
1927
1928         return results;
1929     }
1930
1931     @Override
1932     public  List<UuidAndTitleCache<TaxonBase>> getUuidAndTitleCache(Integer limit, String pattern){
1933         Session session = getSession();
1934         Query query = null;
1935         if (pattern != null){
1936             query = session.createQuery(
1937                   " SELECT tb.uuid, tb.id, tb.titleCache, tb.name.rank "
1938                   + " FROM TaxonBase as tb "
1939                   + " WHERE tb.titleCache LIKE :pattern");
1940             pattern = pattern.replace("*", "%");
1941             pattern = pattern.replace("?", "_");
1942             pattern = pattern + "%";
1943             query.setParameter("pattern", pattern);
1944         } else {
1945             query = session.createQuery(
1946                     " SELECT tb.uuid, taxonBase.id, tb.titleCache, tb.name.rank "
1947                   + " FROM TaxonBase AS tb");
1948         }
1949         if (limit != null){
1950            query.setMaxResults(limit);
1951         }
1952
1953         return getUuidAndTitleCache(query);
1954     }
1955
1956     @Override
1957     protected List<UuidAndTitleCache<TaxonBase>> getUuidAndTitleCache(Query query){
1958         List<UuidAndTitleCache<TaxonBase>> list = new ArrayList<>();
1959
1960         @SuppressWarnings("unchecked")
1961         List<Object[]> result = query.list();
1962         if (!result.isEmpty()){
1963             if (result.iterator().next().length == 4){
1964                 Collections.sort(result, new UuidAndTitleCacheTaxonComparator());
1965             }
1966         }
1967
1968         for(Object[] object : result){
1969             list.add(new UuidAndTitleCache<TaxonBase>((UUID) object[0],(Integer) object[1], (String) object[2]));
1970         }
1971         return list;
1972     }
1973
1974     /**
1975      * {@inheritDoc}
1976      */
1977     @Override
1978     public List<TaxonGraphEdgeDTO> getTaxonGraphEdgeDTOs(UUID fromTaxonUuid, UUID toTaxonUuid, TaxonRelationshipType type,
1979             boolean includeUnpublished) {
1980
1981         Session session = getSession();
1982         String hql = "select new eu.etaxonomy.cdm.persistence.dto.TaxonGraphEdgeDTO(fromT.uuid, fromT.titleCache, toT.uuid, toT.titleCache, c.uuid, c.titleCache) "
1983                 + "FROM TaxonRelationship as tr "
1984                 + "JOIN tr.relatedTo as toT "
1985                 + "JOIN tr.relatedFrom as fromT "
1986                 + "JOIN tr.citation as c "
1987                 + "WHERE tr.type = :reltype AND toT.uuid = :toTaxonUuid AND fromT.uuid = :fromTaxonUuid";
1988
1989         if(!includeUnpublished){
1990             hql += " AND toT.publish is true AND fromT.publish is true";
1991         }
1992
1993         Query query = session.createQuery(hql);
1994         query.setParameter("reltype", type);
1995         query.setParameter("fromTaxonUuid", fromTaxonUuid);
1996         query.setParameter("toTaxonUuid", toTaxonUuid);
1997
1998         List<TaxonGraphEdgeDTO> result = query.list();
1999
2000         return result;
2001     }
2002
2003
2004 }