app-import/src/main/java/eu/etaxonomy/cdm/io/berlinModel/in/BerlinModelTaxonRelationImport.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
  10 package eu.etaxonomy.cdm.io.berlinModel.in;
  11
  12 import static eu.etaxonomy.cdm.io.berlinModel.BerlinModelTransformer.TAX_REL_IS_HETEROTYPIC_SYNONYM_OF;
  13 import static eu.etaxonomy.cdm.io.berlinModel.BerlinModelTransformer.TAX_REL_IS_HOMOTYPIC_SYNONYM_OF;
  14 import static eu.etaxonomy.cdm.io.berlinModel.BerlinModelTransformer.TAX_REL_IS_INCLUDED_IN;
  15 import static eu.etaxonomy.cdm.io.berlinModel.BerlinModelTransformer.TAX_REL_IS_MISAPPLIED_NAME_OF;
  16 import static eu.etaxonomy.cdm.io.berlinModel.BerlinModelTransformer.TAX_REL_IS_PARTIAL_HETEROTYPIC_SYNONYM_OF;
  17 import static eu.etaxonomy.cdm.io.berlinModel.BerlinModelTransformer.TAX_REL_IS_PARTIAL_HOMOTYPIC_SYNONYM_OF;
  18 import static eu.etaxonomy.cdm.io.berlinModel.BerlinModelTransformer.TAX_REL_IS_PARTIAL_SYN_OF;
  19 import static eu.etaxonomy.cdm.io.berlinModel.BerlinModelTransformer.TAX_REL_IS_PROPARTE_HETEROTYPIC_SYNONYM_OF;
  20 import static eu.etaxonomy.cdm.io.berlinModel.BerlinModelTransformer.TAX_REL_IS_PROPARTE_HOMOTYPIC_SYNONYM_OF;
  21 import static eu.etaxonomy.cdm.io.berlinModel.BerlinModelTransformer.TAX_REL_IS_PROPARTE_SYN_OF;
  22 import static eu.etaxonomy.cdm.io.berlinModel.BerlinModelTransformer.TAX_REL_IS_SYNONYM_OF;
  23
  24 import java.sql.ResultSet;
  25 import java.sql.SQLException;
  26 import java.util.HashMap;
  27 import java.util.HashSet;
  28 import java.util.Map;
  29 import java.util.Set;
  30 import java.util.UUID;
  31
  32 import org.apache.commons.lang.StringUtils;
  33 import org.apache.log4j.Logger;
  34 import org.springframework.stereotype.Component;
  35 import org.springframework.transaction.TransactionStatus;
  36
  37 import eu.etaxonomy.cdm.common.ResultWrapper;
  38 import eu.etaxonomy.cdm.io.berlinModel.BerlinModelTransformer;
  39 import eu.etaxonomy.cdm.io.berlinModel.in.validation.BerlinModelTaxonRelationImportValidator;
  40 import eu.etaxonomy.cdm.io.common.IOValidator;
  41 import eu.etaxonomy.cdm.io.common.ResultSetPartitioner;
  42 import eu.etaxonomy.cdm.io.common.Source;
  43 import eu.etaxonomy.cdm.model.common.AnnotatableEntity;
  44 import eu.etaxonomy.cdm.model.common.CdmBase;
  45 import eu.etaxonomy.cdm.model.common.IdentifiableSource;
  46 import eu.etaxonomy.cdm.model.reference.Reference;
  47 import eu.etaxonomy.cdm.model.taxon.Classification;
  48 import eu.etaxonomy.cdm.model.taxon.Synonym;
  49 import eu.etaxonomy.cdm.model.taxon.SynonymType;
  50 import eu.etaxonomy.cdm.model.taxon.Taxon;
  51 import eu.etaxonomy.cdm.model.taxon.TaxonBase;
  52 import eu.etaxonomy.cdm.model.taxon.TaxonNode;
  53 import eu.etaxonomy.cdm.model.taxon.TaxonRelationship;
  54 import eu.etaxonomy.cdm.model.taxon.TaxonRelationshipType;
  55 import eu.etaxonomy.cdm.strategy.exceptions.UnknownCdmTypeException;
  56
  57 /**
  58  * @author a.mueller
  59  * @created 20.03.2008
  60  */
  61 @Component
  62 public class BerlinModelTaxonRelationImport  extends BerlinModelImportBase  {
  63         private static final Logger logger = Logger.getLogger(BerlinModelTaxonRelationImport.class);
  64
  65         public static final String TREE_NAMESPACE = "PTRefFk";
  66
  67         private static int modCount = 30000;
  68         private static final String pluralString = "taxon relations";
  69         private static final String dbTableName = "RelPTaxon";
  70
  71
  72         public BerlinModelTaxonRelationImport(){
  73                 super(dbTableName, pluralString);
  74         }
  75
  76         /**
  77          * Creates a classification for each PTaxon reference which belongs to a taxon that is included at least in one
  78          * <i>taxonomically included</i> relationship
  79          * @param state
  80          * @return
  81          * @throws SQLException
  82          */
  83         private void makeClassifications(BerlinModelImportState state) throws SQLException{
  84                 logger.info("start make classification ...");
  85
  86                 Set<String> idSet = getTreeReferenceIdSet(state);
  87
  88                 //reference map
  89                 String nameSpace = BerlinModelReferenceImport.REFERENCE_NAMESPACE;
  90                 Class<?> cdmClass = Reference.class;
  91                 Map<String, Reference> refMap = (Map<String, Reference>)getCommonService().getSourcedObjectsByIdInSource(cdmClass, idSet, nameSpace);
  92
  93                 String treeName = "Classification - No Name";
  94
  95                 ResultSet rs = state.getConfig().getSource().getResultSet(getClassificationQuery(state)) ;
  96                 int i = 0;
  97                 //for each reference
  98                 try {
  99                         //TODO handle case useSingleClassification = true && sourceSecId = null, which returns no record
 100                         while (rs.next()){
 101
 102                                 try {
 103                                         if ((i++ % modCount) == 0 && i!= 1 ){ logger.info("RelPTaxa handled: " + (i-1));}
 104
 105                                         Integer ptRefFkInt = nullSafeInt(rs,"PTRefFk");
 106                                         String ptRefFk= String.valueOf(ptRefFkInt);
 107                                         Reference ref = refMap.get(ptRefFk);
 108
 109                                         String refCache = rs.getString("RefCache");
 110                                         if (StringUtils.isNotBlank(refCache)){
 111                                                 treeName = refCache;
 112                                         }
 113                                         if (ref != null && StringUtils.isNotBlank(ref.getTitleCache())){
 114                                                 treeName = ref.getTitleCache();
 115                                         }
 116                                         Classification tree = Classification.NewInstance(treeName);
 117                                         tree.setReference(ref);
 118                                         if (i == 1 && state.getConfig().getClassificationUuid() != null){
 119                                                 tree.setUuid(state.getConfig().getClassificationUuid());
 120                                         }
 121                                         IdentifiableSource identifiableSource = IdentifiableSource.NewDataImportInstance(ptRefFk, TREE_NAMESPACE);
 122                                         tree.addSource(identifiableSource);
 123
 124                                         getClassificationService().save(tree);
 125                                         state.putClassificationUuidInt(ptRefFkInt, tree);
 126                                 } catch (Exception e) {
 127                                         logger.error("Error in BerlinModleTaxonRelationImport.makeClassifications: " + e.getMessage());
 128                                         e.printStackTrace();
 129                                 }
 130                         }
 131                 } catch (SQLException e) {
 132                         logger.error("Error in BerlinModleTaxonRelationImport.makeClassifications: " + e.getMessage());
 133                         throw e;
 134                 }
 135                 logger.info("end make classification ...");
 136
 137                 return;
 138         }
 139
 140         /**
 141          * @return
 142          * @throws SQLException
 143          */
 144         private Set<String> getTreeReferenceIdSet(BerlinModelImportState state) throws SQLException {
 145                 Source source = state.getConfig().getSource();
 146                 Set<String> result = new HashSet<String>();
 147                 ResultSet rs = source.getResultSet(getClassificationQuery(state)) ;
 148                 while (rs.next()){
 149                         Object id = rs.getObject("PTRefFk");
 150                         result.add(String.valueOf(id));
 151                 }
 152                 return result;
 153         }
 154
 155         /**
 156          * @return
 157          */
 158         private String getClassificationQuery(BerlinModelImportState state) {
 159                 boolean includeAllClassifications = state.getConfig().isIncludeAllNonMisappliedRelatedClassifications();
 160                 String strQuerySelect = "SELECT PTaxon.PTRefFk, r.RefCache ";
 161                 String strQueryFrom = " FROM RelPTaxon " +
 162                                                         " INNER JOIN PTaxon AS PTaxon ON RelPTaxon.PTNameFk2 = PTaxon.PTNameFk AND RelPTaxon.PTRefFk2 = PTaxon.PTRefFk " +
 163                                                         " INNER JOIN Reference r ON PTaxon.PTRefFk = r.RefId ";
 164                 String strQueryWhere = " WHERE (RelPTaxon.RelQualifierFk = 1) ";
 165                 if (includeAllClassifications){
 166                         strQueryWhere = " WHERE (RelPTaxon.RelQualifierFk <> 3) ";
 167                 }else{
 168                         if (state.getConfig().isUseSingleClassification()){
 169                                 if (state.getConfig().getSourceSecId()!= null){
 170                                         strQueryWhere += " AND PTaxon.PTRefFk = " + state.getConfig().getSourceSecId() +  " ";
 171                                 }else{
 172                                         strQueryWhere += " AND (1=0) ";
 173                                 }
 174                         }
 175                 }
 176
 177                 String strQueryGroupBy = " GROUP BY PTaxon.PTRefFk, r.RefCache ";
 178                 String strQuery = strQuerySelect + " " + strQueryFrom + " " + strQueryWhere + " " + strQueryGroupBy;
 179
 180
 181                 if (includeAllClassifications){
 182                         //add otherdirection
 183                         strQuerySelect = "SELECT PTaxon.PTRefFk, r.RefCache ";
 184                         strQueryFrom = " FROM RelPTaxon rel " +
 185                                                                 " INNER JOIN PTaxon AS PTaxon ON rel.PTNameFk1 = PTaxon.PTNameFk AND rel.PTRefFk1 = PTaxon.PTRefFk " +
 186                                                                 " INNER JOIN Reference r ON PTaxon.PTRefFk = r.RefId ";
 187                         strQueryWhere =" WHERE (rel.RelQualifierFk <> 3) ";
 188                         String strAllQuery =  strQuerySelect + " " + strQueryFrom + " " + strQueryWhere + " " + strQueryGroupBy;
 189                         strQuery = strQuery + " UNION " + strAllQuery;
 190                 }
 191
 192
 193
 194                 boolean includeFlatClassifications = state.getConfig().isIncludeFlatClassifications();
 195                 //concepts with
 196                 if (includeFlatClassifications){
 197                         String strFlatQuery =
 198                                         " SELECT pt.PTRefFk AS secRefFk, r.RefCache AS secRef " +
 199                                         " FROM PTaxon AS pt LEFT OUTER JOIN " +
 200                                                   " Reference r ON pt.PTRefFk = r.RefId LEFT OUTER JOIN " +
 201                                                   " RelPTaxon rel1 ON pt.PTNameFk = rel1.PTNameFk2 AND pt.PTRefFk = rel1.PTRefFk2 LEFT OUTER JOIN " +
 202                                                   " RelPTaxon AS rel2 ON pt.PTNameFk = rel2.PTNameFk1 AND pt.PTRefFk = rel2.PTRefFk1 " +
 203                                         " WHERE (rel2.RelQualifierFk IS NULL) AND (rel1.RelQualifierFk IS NULL) " +
 204                                         " GROUP BY pt.PTRefFk, r.RefCache "
 205                                         ;
 206
 207                         strQuery = strQuery + " UNION " + strFlatQuery;
 208                 }
 209
 210
 211
 212                 if (state.getConfig().getClassificationQuery() != null){
 213                         strQuery = state.getConfig().getClassificationQuery();
 214                 }
 215                 return strQuery;
 216         }
 217
 218         @Override
 219         protected String getRecordQuery(BerlinModelImportConfigurator config) {
 220                 String strQuery =
 221                         " SELECT RelPTaxon.*, FromTaxon.RIdentifier as taxon1Id, ToTaxon.RIdentifier as taxon2Id, ToTaxon.PTRefFk as treeRefFk, FromTaxon.PTRefFk as fromRefFk, q.is_concept_relation " +
 222                         " FROM PTaxon as FromTaxon " +
 223                 " INNER JOIN RelPTaxon ON FromTaxon.PTNameFk = RelPTaxon.PTNameFk1 AND FromTaxon.PTRefFk = RelPTaxon.PTRefFk1 " +
 224                 " INNER JOIN PTaxon AS ToTaxon ON RelPTaxon.PTNameFk2 = ToTaxon.PTNameFk AND RelPTaxon.PTRefFk2 = ToTaxon.PTRefFk " +
 225                 " INNER JOIN RelPTQualifier q ON q.RelPTQualifierId = RelPTaxon.RelQualifierFk " +
 226             " WHERE RelPTaxon.RelPTaxonId IN ("+ID_LIST_TOKEN+") ORDER BY RelPTaxon.RelPTaxonId ";
 227                 return strQuery;
 228         }
 229
 230         @Override
 231         public boolean doPartition(ResultSetPartitioner partitioner, BerlinModelImportState state) {
 232                 boolean success = true ;
 233                 BerlinModelImportConfigurator config = state.getConfig();
 234                 Set<TaxonBase> taxaToSave = new HashSet<TaxonBase>();
 235                 Map<String, TaxonBase> taxonMap = partitioner.getObjectMap(BerlinModelTaxonImport.NAMESPACE);
 236                 Map<Integer, Classification> classificationMap = new HashMap<Integer, Classification>();
 237                 Map<String, Reference> refMap = partitioner.getObjectMap(BerlinModelReferenceImport.REFERENCE_NAMESPACE);
 238
 239                 ResultSet rs = partitioner.getResultSet();
 240
 241                 try{
 242                         int i = 0;
 243                         //for each reference
 244                         while (rs.next()){
 245
 246                                 if ((i++ % modCount) == 0 && i!= 1 ){ logger.info("RelPTaxa handled: " + (i-1));}
 247
 248                                 int relPTaxonId = rs.getInt("RelPTaxonId");
 249                                 Integer taxon1Id = nullSafeInt(rs, "taxon1Id");
 250                                 Integer taxon2Id = nullSafeInt(rs, "taxon2Id");
 251                                 int relQualifierFk = 0;
 252                                 try {
 253                                         Integer relRefFk = nullSafeInt(rs,"relRefFk");
 254                                         int treeRefFk = rs.getInt("treeRefFk");
 255                                         int fromRefFk = rs.getInt("fromRefFk");
 256
 257                                         relQualifierFk = rs.getInt("relQualifierFk");
 258                                         String notes = rs.getString("notes");
 259                                         boolean isConceptRelationship = rs.getBoolean("is_concept_relation");
 260
 261                                         TaxonBase<?> taxon1 = taxonMap.get(String.valueOf(taxon1Id));
 262                                         TaxonBase<?> taxon2 = taxonMap.get(String.valueOf(taxon2Id));
 263
 264                                         String refFk = String.valueOf(relRefFk);
 265                                         Reference citation = refMap.get(refFk);
 266
 267                                         String microcitation = null; //does not exist in RelPTaxon
 268
 269                                         if (taxon2 != null && taxon1 != null){
 270                                                 if (!(taxon2 instanceof Taxon)){
 271                                                         logger.error("ToTaxon (ID = " + taxon2.getId()+ ", RIdentifier = " + taxon2Id + ") can't be casted to Taxon. RelPTaxon: " + relPTaxonId );
 272                                                         success = false;
 273                                                         continue;
 274                                                 }
 275                                                 AnnotatableEntity taxonRelationship = null;
 276                                                 Taxon toTaxon = (Taxon)taxon2;
 277                                                 if (isTaxonRelationship(relQualifierFk)){
 278                                                         if (!(taxon1 instanceof Taxon)){
 279                                                                 logger.error("TaxonBase (ID = " + taxon1.getId()+ ", RIdentifier = " + taxon1Id + ") for TaxonRelation ("+relPTaxonId+") can't be casted to Taxon");
 280                                                                 success = false;
 281                                                                 continue;
 282                                                         }
 283                                                         Taxon fromTaxon = (Taxon)taxon1;
 284                                                         if (relQualifierFk == TAX_REL_IS_INCLUDED_IN){
 285                                                                 taxonRelationship = makeTaxonomicallyIncluded(state, classificationMap, treeRefFk, fromTaxon, toTaxon, citation, microcitation);
 286                                                         }else if (relQualifierFk == TAX_REL_IS_MISAPPLIED_NAME_OF){
 287                                                                  taxonRelationship = toTaxon.addMisappliedName(fromTaxon, citation, microcitation);
 288                                                         }else{
 289                                                                 handleAllRelatedTaxa(state, fromTaxon, classificationMap, fromRefFk);
 290                                                                 handleAllRelatedTaxa(state, toTaxon, classificationMap, treeRefFk);
 291                                                                 logger.warn("Unhandled taxon relationship: RelId:" + relPTaxonId + "; QualifierId: " + relQualifierFk);
 292                                                         }
 293                                                 }else if (isSynonymRelationship(relQualifierFk)){
 294                                                         if (!(taxon1 instanceof Synonym)){
 295                                                                 logger.warn("Validated: Taxon (ID = " + taxon1.getId()+ ", RIdentifier = " + taxon1Id + ") can't be casted to Synonym");
 296                                                                 success = false;
 297                                                                 continue;
 298                                                         }
 299                                                         handleAllRelatedTaxa(state, toTaxon, classificationMap, treeRefFk);
 300                                                         Synonym synonym = (Synonym)taxon1;
 301                                                         makeSynRel(relQualifierFk, toTaxon, synonym, citation, microcitation);
 302                                                         if (isNotBlank(notes)){
 303                                     logger.warn("Notes for synonym relationship or unknown taxon relationship not handled");
 304                                 }
 305
 306                                                         if (relQualifierFk == TAX_REL_IS_SYNONYM_OF ||
 307                                                                         relQualifierFk == TAX_REL_IS_HOMOTYPIC_SYNONYM_OF ||
 308                                                                         relQualifierFk == TAX_REL_IS_HETEROTYPIC_SYNONYM_OF){
 309                                                                 //pro parte already set by taxon mapping
 310                                                         }else if (relQualifierFk == TAX_REL_IS_PROPARTE_SYN_OF ||
 311                                                                         relQualifierFk == TAX_REL_IS_PROPARTE_HOMOTYPIC_SYNONYM_OF ||
 312                                                                         relQualifierFk == TAX_REL_IS_PROPARTE_HETEROTYPIC_SYNONYM_OF ){
 313                                                                         synonym.setProParte(true);
 314                                                         }else if(relQualifierFk == TAX_REL_IS_PARTIAL_SYN_OF ||
 315                                                                         relQualifierFk == TAX_REL_IS_PARTIAL_HOMOTYPIC_SYNONYM_OF ||
 316                                                                         relQualifierFk == TAX_REL_IS_PARTIAL_HETEROTYPIC_SYNONYM_OF ){
 317                                                                 synonym.setPartial(true);
 318                                                         }else{
 319                                                                 success = false;
 320                                                                 logger.warn("Proparte/Partial not yet implemented for TaxonRelationShipType " + relQualifierFk);
 321                                                         }
 322                                                 }else if (isConceptRelationship){
 323                                                         ResultWrapper<Boolean> isInverse = ResultWrapper.NewInstance(false);
 324                                                         ResultWrapper<Boolean> isDoubtful = ResultWrapper.NewInstance(false);
 325                                                         try {
 326                                                                 TaxonRelationshipType relType = BerlinModelTransformer.taxonRelId2TaxonRelType(relQualifierFk, isInverse, isDoubtful);
 327
 328                                                                 if (! (taxon1 instanceof Taxon)){
 329                                                                         success = false;
 330                                                                         logger.error("TaxonBase (ID = " + taxon1.getId()+ ", RIdentifier = " + taxon1Id + ") can't be casted to Taxon");
 331                                                                 }else{
 332                                                                         Taxon fromTaxon = (Taxon)taxon1;
 333                                                                         if (isInverse.getValue() == true){
 334                                                                                 Taxon tmp = fromTaxon;
 335                                                                                 fromTaxon = toTaxon;
 336                                                                                 toTaxon = tmp;
 337                                                                         }
 338                                                                         taxonRelationship = fromTaxon.addTaxonRelation(toTaxon, relType, citation, microcitation);
 339                                                                         handleAllRelatedTaxa(state, toTaxon, classificationMap, treeRefFk);
 340                                                                         handleAllRelatedTaxa(state, fromTaxon, classificationMap, fromRefFk);
 341                                                                         if (isDoubtful.getValue() == true){
 342                                                                                 ((TaxonRelationship)taxonRelationship).setDoubtful(true);
 343                                                                         }
 344                                                                 }
 345                                                         } catch (UnknownCdmTypeException e) {
 346                                                                 logger.warn("TaxonRelationShipType " + relQualifierFk + " (conceptRelationship) not yet implemented");
 347                                                                  success = false;
 348                                                         }
 349                                                 }else {
 350                                                         logger.warn("TaxonRelationShipType " + relQualifierFk + " not yet implemented: RelPTaxonId = " + relPTaxonId );
 351                                                         success = false;
 352                                                 }
 353
 354                                                 if (taxonRelationship != null){
 355                                                     doNotes(taxonRelationship, notes);
 356                                                 }else if (isNotBlank(notes)){
 357                                                     logger.warn("Notes for synonym relationship or unknown taxon relationship not handled");
 358                                                 }
 359                                                 taxaToSave.add(taxon2);
 360
 361                                                 //TODO
 362                                                 //etc.
 363                                         }else{
 364                                                 if (taxon2 != null && taxon1 == null){
 365                                                         logger.warn("First taxon ("+taxon1Id+") for RelPTaxon " + relPTaxonId + " does not exist in store. RelType: " + relQualifierFk);
 366                                                 }else if (taxon2 == null && taxon1 != null){
 367                                                         logger.warn("Second taxon ("+taxon2Id +") for RelPTaxon " + relPTaxonId + " does not exist in store. RelType: " + relQualifierFk);
 368                                                 }else{
 369                                                         logger.warn("Both taxa ("+taxon1Id+","+taxon2Id +") for RelPTaxon " + relPTaxonId + " do not exist in store. RelType: " + relQualifierFk);
 370                                                 }
 371
 372                                                 success = false;
 373                                         }
 374                                 } catch (Exception e) {
 375                                         logger.error("Exception occurred when trying to handle taxon relationship " + relPTaxonId + " relQualifierFK " + relQualifierFk + " (" + taxon1Id + ","+ taxon2Id + "): " + e.getMessage());
 376                                         e.printStackTrace();
 377                                 }
 378                         }
 379                 }catch(SQLException e){
 380                         throw new RuntimeException(e);
 381                 }
 382                 logger.info("Taxa to save: " + taxaToSave.size());
 383                 partitioner.startDoSave();
 384                 getTaxonService().saveOrUpdate(taxaToSave);
 385                 classificationMap = null;
 386                 taxaToSave = null;
 387
 388                 return success;
 389         }
 390
 391
 392         private void handleAllRelatedTaxa(BerlinModelImportState state, Taxon taxon, Map<Integer, Classification> classificationMap, Integer secRefFk) {
 393                 if (taxon.getTaxonNodes().size() > 0){
 394                         return;
 395                 }else{
 396                         Classification classification = getClassificationTree(state, classificationMap, secRefFk);
 397                         classification.addChildTaxon(taxon, null, null);
 398                 }
 399         }
 400
 401         @Override
 402         protected void doInvoke(BerlinModelImportState state){
 403                 try {
 404                         makeClassifications(state);
 405                         super.doInvoke(state);
 406                         makeFlatClassificationTaxa(state);
 407                         return;
 408                 } catch (SQLException e) {
 409                         throw new RuntimeException(e);
 410                 }
 411
 412         }
 413
 414
 415         private void makeFlatClassificationTaxa(BerlinModelImportState state) {
 416                 //Note: this part still does not use partitions
 417                 logger.info("Flat classifications start");
 418                 TransactionStatus txStatus = startTransaction();
 419                 if (! state.getConfig().isIncludeFlatClassifications()){
 420                         return;
 421                 }
 422                 String sql = " SELECT pt.PTRefFk AS secRefFk, pt.RIdentifier " +
 423                                                 " FROM PTaxon AS pt " +
 424                                                         " LEFT OUTER JOIN RelPTaxon ON pt.PTNameFk = RelPTaxon.PTNameFk2 AND pt.PTRefFk = RelPTaxon.PTRefFk2 " +
 425                                                         "  LEFT OUTER JOIN RelPTaxon AS RelPTaxon_1 ON pt.PTNameFk = RelPTaxon_1.PTNameFk1 AND pt.PTRefFk = RelPTaxon_1.PTRefFk1 " +
 426                                                 " WHERE (RelPTaxon_1.RelQualifierFk IS NULL) AND (dbo.RelPTaxon.RelQualifierFk IS NULL) " +
 427                                                 " ORDER BY pt.PTRefFk " ;
 428                 ResultSet rs = state.getConfig().getSource().getResultSet(sql);
 429                 Map<Object, Map<String, ? extends CdmBase>> maps = getRelatedObjectsForFlatPartition(rs);
 430
 431                 Map<String, TaxonBase> taxonMap = (Map<String, TaxonBase>) maps.get(BerlinModelTaxonImport.NAMESPACE);
 432                 Map<Integer, Classification> classificationMap = new HashMap<Integer, Classification>();
 433
 434                 rs = state.getConfig().getSource().getResultSet(sql);
 435                 try {
 436                         while (rs.next()){
 437                                 Integer treeRefFk = rs.getInt("secRefFk");
 438                                 String taxonId = rs.getString("RIdentifier");
 439                                 Classification classification = getClassificationTree(state, classificationMap, treeRefFk);
 440                                 TaxonBase<?> taxon = taxonMap.get(taxonId);
 441                                 if (taxon == null){
 442                                         String message = "TaxonBase for taxon id (%s) not found in taxonMap";
 443                                         logger.warn(String.format(message, taxonId, taxonId));
 444                                 }else if (taxon.isInstanceOf(Taxon.class)){
 445                                         classification.addChildTaxon(CdmBase.deproxy(taxon, Taxon.class), null, null);
 446                                 }else{
 447                                         String message = "TaxonBase for taxon is not of class Taxon but %s (RIdentifier %s)";
 448                                         logger.warn(String.format(message, taxon.getClass(), taxonId));
 449                                 }
 450                         }
 451                 } catch (SQLException e) {
 452                         // TODO Auto-generated catch block
 453                         e.printStackTrace();
 454                 }
 455                 commitTransaction(txStatus);
 456                 logger.info("Flat classifications end");
 457
 458         }
 459
 460         @Override
 461         protected String getIdQuery(BerlinModelImportState state) {
 462                 if (state.getConfig().getRelTaxaIdQuery() != null){
 463                         return state.getConfig().getRelTaxaIdQuery();
 464                 }else{
 465                         return super.getIdQuery(state);
 466                 }
 467         }
 468
 469         @Override
 470         public Map<Object, Map<String, ? extends CdmBase>> getRelatedObjectsForPartition( ResultSet rs, BerlinModelImportState state) {
 471                 String nameSpace;
 472                 Class<?> cdmClass;
 473                 Set<String> idSet;
 474                 Map<Object, Map<String, ? extends CdmBase>> result = new HashMap<Object, Map<String, ? extends CdmBase>>();
 475
 476                 try{
 477                         Set<String> taxonIdSet = new HashSet<String>();
 478                         Set<String> referenceIdSet = new HashSet<String>();
 479 //                      Set<String> classificationIdSet = new HashSet<String>();
 480                         while (rs.next()){
 481                                 handleForeignKey(rs, taxonIdSet, "taxon1Id");
 482                                 handleForeignKey(rs, taxonIdSet, "taxon2Id");
 483 //                              handleForeignKey(rs, classificationIdSet, "treeRefFk");
 484                                 handleForeignKey(rs, referenceIdSet, "RelRefFk");
 485         }
 486
 487                         //taxon map
 488                         nameSpace = BerlinModelTaxonImport.NAMESPACE;
 489                         cdmClass = TaxonBase.class;
 490                         idSet = taxonIdSet;
 491                         Map<String, TaxonBase> taxonMap = (Map<String, TaxonBase>)getCommonService().getSourcedObjectsByIdInSource(cdmClass, idSet, nameSpace);
 492                         result.put(nameSpace, taxonMap);
 493
 494 //                      //tree map
 495 //                      nameSpace = "Classification";
 496 //                      cdmClass = Classification.class;
 497 //                      idSet = classificationIdSet;
 498 //                      Map<String, Classification> treeMap = (Map<String, Classification>)getCommonService().getSourcedObjectsByIdInSource(cdmClass, idSet, nameSpace);
 499 //                      result.put(cdmClass, treeMap);
 500 //                      Set<UUID> treeUuidSet = state
 501 //                      getClassificationService().find(uuidSet);
 502 //
 503                         //reference map
 504                         nameSpace = BerlinModelReferenceImport.REFERENCE_NAMESPACE;
 505                         cdmClass = Reference.class;
 506                         idSet = referenceIdSet;
 507                         Map<String, Reference> referenceMap = (Map<String, Reference>)getCommonService().getSourcedObjectsByIdInSource(cdmClass, idSet, nameSpace);
 508                         result.put(nameSpace, referenceMap);
 509
 510                 } catch (SQLException e) {
 511                         throw new RuntimeException(e);
 512                 }
 513                 return result;
 514         }
 515
 516
 517         private Map<Object, Map<String, ? extends CdmBase>> getRelatedObjectsForFlatPartition( ResultSet rs) {
 518                 String nameSpace;
 519                 Class cdmClass;
 520                 Set<String> idSet;
 521                 Map<Object, Map<String, ? extends CdmBase>> result = new HashMap<Object, Map<String, ? extends CdmBase>>();
 522
 523                 try{
 524                         Set<String> taxonIdSet = new HashSet<String>();
 525                         Set<String> referenceIdSet = new HashSet<String>();
 526 //                      Set<String> classificationIdSet = new HashSet<String>();
 527                         while (rs.next()){
 528                                 handleForeignKey(rs, taxonIdSet, "RIdentifier");
 529 //                              handleForeignKey(rs, classificationIdSet, "treeRefFk");
 530         }
 531
 532                         //taxon map
 533                         nameSpace = BerlinModelTaxonImport.NAMESPACE;
 534                         cdmClass = TaxonBase.class;
 535                         idSet = taxonIdSet;
 536                         Map<String, TaxonBase> taxonMap = (Map<String, TaxonBase>)getCommonService().getSourcedObjectsByIdInSource(cdmClass, idSet, nameSpace);
 537                         result.put(nameSpace, taxonMap);
 538
 539 //                      //tree map
 540 //                      nameSpace = "Classification";
 541 //                      cdmClass = Classification.class;
 542 //                      idSet = classificationIdSet;
 543 //                      Map<String, Classification> treeMap = (Map<String, Classification>)getCommonService().getSourcedObjectsByIdInSource(cdmClass, idSet, nameSpace);
 544 //                      result.put(cdmClass, treeMap);
 545 //                      Set<UUID> treeUuidSet = state
 546 //                      getClassificationService().find(uuidSet);
 547 //
 548
 549                 } catch (SQLException e) {
 550                         throw new RuntimeException(e);
 551                 }
 552                 return result;
 553         }
 554
 555
 556         private void makeSynRel (int relQualifierFk, Taxon toTaxon, Synonym synonym, Reference citation, String microcitation){
 557                 if (citation != null && !citation.equals(synonym.getSec())){
 558                     logger.warn("A synonym relationship citation is given and differs from synonym secundum. This can not be handled in CDM");
 559                 }
 560                 if (isNotBlank(microcitation)  && !microcitation.equals(synonym.getSecMicroReference())){
 561             logger.warn("A synonym relationship microcitation is given and differs from synonym secundum micro reference. This can not be handled in CDM");
 562         }
 563             if (relQualifierFk == TAX_REL_IS_HOMOTYPIC_SYNONYM_OF ||
 564                                 relQualifierFk == TAX_REL_IS_PROPARTE_HOMOTYPIC_SYNONYM_OF ||
 565                                 relQualifierFk == TAX_REL_IS_PARTIAL_HOMOTYPIC_SYNONYM_OF){
 566                         toTaxon.addHomotypicSynonym(synonym);
 567                 }else if (relQualifierFk == TAX_REL_IS_HETEROTYPIC_SYNONYM_OF ||
 568                                 relQualifierFk == TAX_REL_IS_PROPARTE_HETEROTYPIC_SYNONYM_OF ||
 569                                 relQualifierFk == TAX_REL_IS_PARTIAL_HETEROTYPIC_SYNONYM_OF){
 570                         toTaxon.addSynonym(synonym, SynonymType.HETEROTYPIC_SYNONYM_OF());
 571                 }else if (relQualifierFk == TAX_REL_IS_SYNONYM_OF ||
 572                                 relQualifierFk == TAX_REL_IS_PROPARTE_SYN_OF ||
 573                                 relQualifierFk == TAX_REL_IS_PARTIAL_SYN_OF){
 574                         toTaxon.addSynonym(synonym, SynonymType.SYNONYM_OF());
 575                 }else{
 576                         logger.warn("SynonymyRelationShipType could not be defined for relQualifierFk " + relQualifierFk + ". 'Unknown'-Type taken instead.");
 577                         toTaxon.addSynonym(synonym, SynonymType.SYNONYM_OF());
 578                 }
 579                 return;
 580
 581         }
 582
 583         private  boolean isSynonymRelationship(int relQualifierFk){
 584                 if (relQualifierFk == TAX_REL_IS_SYNONYM_OF ||
 585                         relQualifierFk == TAX_REL_IS_HOMOTYPIC_SYNONYM_OF ||
 586                         relQualifierFk == TAX_REL_IS_HETEROTYPIC_SYNONYM_OF ||
 587                         relQualifierFk == TAX_REL_IS_PROPARTE_SYN_OF ||
 588                         relQualifierFk == TAX_REL_IS_PARTIAL_SYN_OF ||
 589                         relQualifierFk == TAX_REL_IS_PROPARTE_HOMOTYPIC_SYNONYM_OF ||
 590                         relQualifierFk == TAX_REL_IS_PROPARTE_HETEROTYPIC_SYNONYM_OF ||
 591                         relQualifierFk == TAX_REL_IS_PARTIAL_HOMOTYPIC_SYNONYM_OF ||
 592                         relQualifierFk == TAX_REL_IS_PARTIAL_HETEROTYPIC_SYNONYM_OF
 593                 ){
 594                         return true;
 595                 }else{
 596                         return false;
 597                 }
 598         }
 599
 600         private  boolean isTaxonRelationship(int relQualifierFk){
 601                 if (relQualifierFk == TAX_REL_IS_INCLUDED_IN ||
 602                                 relQualifierFk == TAX_REL_IS_MISAPPLIED_NAME_OF){
 603                         return true;
 604                 }else{
 605                         return false;
 606                 }
 607         }
 608
 609         private TaxonNode makeTaxonomicallyIncluded(BerlinModelImportState state, Map<Integer, Classification> classificationMap, int treeRefFk, Taxon child, Taxon parent, Reference citation, String microCitation){
 610                 Classification tree = getClassificationTree(state, classificationMap, treeRefFk);
 611                 return tree.addParentChild(parent, child, citation, microCitation);
 612         }
 613
 614         private Classification getClassificationTree(BerlinModelImportState state, Map<Integer, Classification> classificationMap, int treeRefFk) {
 615                 if (state.getConfig().isUseSingleClassification()){
 616                         if (state.getConfig().getSourceSecId() != null){
 617                                 treeRefFk = (Integer)state.getConfig().getSourceSecId();
 618                         }else{
 619                                 treeRefFk = 1;
 620                         }
 621
 622                 }
 623                 Classification tree = classificationMap.get(treeRefFk);
 624                 if (tree == null){
 625                         UUID treeUuid = state.getTreeUuidByIntTreeKey(treeRefFk);
 626                         if (treeUuid == null){
 627                                 throw new IllegalStateException("treeUUID does not exist in state for " + treeRefFk );
 628                         }
 629                         tree = getClassificationService().find(treeUuid);
 630                         classificationMap.put(treeRefFk, tree);
 631                 }
 632                 if (tree == null){
 633                         throw new IllegalStateException("Tree for ToTaxon reference " + treeRefFk + " does not exist.");
 634                 }
 635                 return tree;
 636         }
 637
 638         @Override
 639         protected boolean doCheck(BerlinModelImportState state){
 640                 IOValidator<BerlinModelImportState> validator = new BerlinModelTaxonRelationImportValidator();
 641                 return validator.validate(state);
 642         }
 643
 644         @Override
 645         protected boolean isIgnore(BerlinModelImportState state){
 646                 return ! state.getConfig().isDoRelTaxa();
 647         }
 648
 649
 650 }