cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/SingleTermUpdater.java

   1 // $Id$
   2 /**
   3 * Copyright (C) 2009 EDIT
   4 * European Distributed Institute of Taxonomy
   5 * http://www.e-taxonomy.eu
   6 *
   7 * The contents of this file are subject to the Mozilla Public License Version 1.1
   8 * See LICENSE.TXT at the top of this package for the full license terms.
   9 */
  10 package eu.etaxonomy.cdm.database.update;
  11
  12 import java.sql.ResultSet;
  13 import java.sql.SQLException;
  14 import java.util.UUID;
  15
  16 import org.apache.log4j.Logger;
  17
  18 import eu.etaxonomy.cdm.common.monitor.IProgressMonitor;
  19 import eu.etaxonomy.cdm.database.ICdmDataSource;
  20 import eu.etaxonomy.cdm.model.common.TermType;
  21 import eu.etaxonomy.cdm.model.description.Feature;
  22 import eu.etaxonomy.cdm.model.name.Rank;
  23 import eu.etaxonomy.cdm.model.name.RankClass;
  24
  25 /**
  26  * Creates a new term if a term with the same given uuid does not exist yet
  27  * @author a.mueller
  28  * @date 10.09.2010
  29  *
  30  */
  31 public class SingleTermUpdater extends SchemaUpdaterStepBase<SingleTermUpdater> implements ITermUpdaterStep{
  32         @SuppressWarnings("unused")
  33         private static final Logger logger = Logger.getLogger(SingleTermUpdater.class);
  34
  35         /**
  36          * @Deprecated use {@link #NewInstance(String, TermType, UUID, String, String, String, String, UUID, UUID, boolean, UUID)} instead
  37          */
  38         @Deprecated
  39         public static final SingleTermUpdater NewInstance(String stepName, UUID uuidTerm, String description,  String label, String abbrev, String dtype, UUID uuidVocabulary, UUID uuidLanguage, boolean isOrdered, UUID uuidAfterTerm){
  40                 return new SingleTermUpdater(stepName, null, uuidTerm, null, description, label, abbrev, dtype, uuidVocabulary, uuidLanguage, isOrdered, uuidAfterTerm);
  41         }
  42
  43         public static final SingleTermUpdater NewInstance(String stepName, TermType termType, UUID uuidTerm, String idInVocabulary, String description,  String label, String abbrev, String dtype, UUID uuidVocabulary, UUID uuidLanguage, boolean isOrdered, UUID uuidAfterTerm){
  44                 return new SingleTermUpdater(stepName, termType, uuidTerm, idInVocabulary, description, label, abbrev, dtype, uuidVocabulary, uuidLanguage, isOrdered, uuidAfterTerm);
  45         }
  46
  47
  48         private UUID uuidTerm ;
  49         private String description;
  50         private String label;
  51         private String abbrev;
  52         private String dtype;
  53         private UUID uuidVocabulary;
  54         private boolean isOrdered;
  55         private UUID uuidAfterTerm;
  56         private UUID uuidLanguage;
  57         private String reverseDescription;
  58         private String reverseLabel;
  59         private String reverseAbbrev;
  60         private RankClass rankClass;
  61         private TermType termType;
  62         private String idInVocabulary;
  63         private boolean symmetric = false;
  64         private boolean transitive = false;
  65
  66
  67
  68         private SingleTermUpdater(String stepName, TermType termType, UUID uuidTerm, String idInVocabulary, String description, String label, String abbrev, String dtype, UUID uuidVocabulary, UUID uuidLanguage, boolean isOrdered, UUID uuidAfterTerm) {
  69                 super(stepName);
  70                 this.termType = termType;
  71                 this.idInVocabulary = idInVocabulary;
  72                 this.abbrev = abbrev;
  73                 this.description = description;
  74                 this.dtype = dtype;
  75                 this.label = label;
  76                 this.isOrdered = isOrdered;
  77                 this.uuidTerm = uuidTerm;
  78                 this.uuidVocabulary = uuidVocabulary;
  79                 this.uuidAfterTerm = uuidAfterTerm;
  80                 this.uuidLanguage = uuidLanguage;
  81         }
  82
  83         @Override
  84         public Integer invoke(ICdmDataSource datasource, IProgressMonitor monitor) throws SQLException{
  85                 String sqlCheckTermExists = " SELECT count(*) as n FROM DefinedTermBase WHERE uuid = '" + uuidTerm + "'";
  86                 Long n = (Long)datasource.getSingleValue(sqlCheckTermExists);
  87                 if (n != 0){
  88                         monitor.warning("Term already exists: " + label + "(" + uuidTerm + ")");
  89                         return -1;
  90                 }
  91
  92                 //vocabulary id
  93                 int vocId;
  94                 String sqlVocId = " SELECT id FROM TermVocabulary WHERE uuid = '" + uuidVocabulary + "'";
  95                 ResultSet rs = datasource.executeQuery(sqlVocId);
  96                 if (rs.next()){
  97                         vocId = rs.getInt("id");
  98                 }else{
  99                         String warning = "Vocabulary ( "+ uuidVocabulary +" ) for term does not exist!";
 100                         monitor.warning(warning);
 101                         return null;
 102                 }
 103
 104                 Integer termId;
 105                 String sqlMaxId = " SELECT max(id)+1 as maxId FROM DefinedTermBase";
 106                 rs = datasource.executeQuery(sqlMaxId);
 107                 if (rs.next()){
 108                         termId = rs.getInt("maxId");
 109                 }else{
 110                         String warning = "No defined terms do exist yet. Can't update terms!";
 111                         monitor.warning(warning);
 112                         return null;
 113                 }
 114
 115                 String id = Integer.toString(termId);
 116                 String created = getNowString();
 117                 String defaultColor = "null";
 118                 String protectedTitleCache = getBoolean(false, datasource);
 119                 String orderIndex;
 120                 if (isOrdered){
 121                         orderIndex = getOrderIndex(datasource, vocId, monitor);
 122                 }else{
 123                         orderIndex = "null";
 124                 }
 125                 String titleCache = label != null ? label : (abbrev != null ? abbrev : description );
 126                 String idInVocStr = idInVocabulary == null ? "NULL" : "'" + idInVocabulary + "'";
 127                 String sqlInsertTerm = " INSERT INTO DefinedTermBase (DTYPE, id, uuid, created, termtype, idInVocabulary, protectedtitlecache, titleCache, orderindex, defaultcolor, vocabulary_id)" +
 128                                 "VALUES ('" + dtype + "', " + id + ", '" + uuidTerm + "', '" + created + "', '" + termType.getKey() + "', " + idInVocStr +  ", " + protectedTitleCache + ", '" + titleCache + "', " + orderIndex + ", " + defaultColor + ", " + vocId + ")";
 129                 datasource.executeUpdate(sqlInsertTerm);
 130
 131                 updateFeatureTerms(termId, datasource, monitor);
 132                 updateRelationshipTerms(termId, datasource, monitor);
 133                 updateRanks(termId, datasource, monitor);
 134
 135 //
 136 //              INSERT INTO DefinedTermBase (DTYPE, id, uuid, created, protectedtitlecache, titleCache, orderindex, defaultcolor, vocabulary_id)
 137 //              SELECT 'ReferenceSystem' ,  (@defTermId := max(id)+1)  as maxId , '1bb67042-2814-4b09-9e76-c8c1e68aa281', '2010-06-01 10:15:00', b'0', 'Google Earth', null, null, @refSysVocId
 138 //              FROM DefinedTermBase ;
 139 //
 140
 141                 //language id
 142                 Integer langId = getLanguageId(uuidLanguage, datasource, monitor);
 143                 if (langId == null){
 144                         return null;
 145                 }
 146
 147                 //representation
 148                 int repId;
 149                 sqlMaxId = " SELECT max(id)+1 as maxId FROM Representation";
 150                 rs = datasource.executeQuery(sqlMaxId);
 151                 if (rs.next()){
 152                         repId = rs.getInt("maxId");
 153                 }else{
 154                         String warning = "No representations do exist yet. Can't update terms!";
 155                         monitor.warning(warning);
 156                         return null;
 157                 }
 158
 159                 //standard representation
 160                 UUID uuidRepresentation = UUID.randomUUID();
 161                 String sqlInsertRepresentation = " INSERT INTO Representation (id, created, uuid, text, label, abbreviatedlabel, language_id) " +
 162                                 "VALUES (" + repId + ", '" + created + "', '" + uuidRepresentation + "', " + nullSafeStr(description) +  ", " +nullSafeStr( label) +  ", " + nullSafeStr(abbrev) +  ", " + langId + ")";
 163
 164                 datasource.executeUpdate(sqlInsertRepresentation);
 165
 166                 String sqlInsertMN = "INSERT INTO DefinedTermBase_Representation (DefinedTermBase_id, representations_id) " +
 167                                 " VALUES ("+ termId +"," +repId+ " )";
 168
 169                 datasource.executeUpdate(sqlInsertMN);
 170
 171                 //reverse representation
 172                 if (hasReverseRepresentation()){
 173                         int reverseRepId = repId + 1;
 174                         UUID uuidReverseRepresentation = UUID.randomUUID();
 175                         String sqlInsertReverseRepresentation = " INSERT INTO Representation (id, created, uuid, text, label, abbreviatedlabel, language_id) " +
 176                                         "VALUES (" + reverseRepId + ", '" + created + "', '" + uuidReverseRepresentation + "', " + nullSafeStr(reverseDescription) +  ", " + nullSafeStr(reverseLabel) +  ",  " + nullSafeStr(reverseAbbrev) +  ", " + langId + ")";
 177
 178                         datasource.executeUpdate(sqlInsertReverseRepresentation);
 179
 180                         String sqlReverseInsertMN = "INSERT INTO RelationshipTermBase_inverseRepresentation (DefinedTermBase_id, inverserepresentations_id) " +
 181                                         " VALUES ("+ termId +"," +reverseRepId+ " )";
 182
 183                         datasource.executeUpdate(sqlReverseInsertMN);
 184                 }
 185
 186                 return termId;
 187         }
 188
 189         private String nullSafeStr(String str) {
 190                 if (str == null){
 191                         return " NULL ";
 192                 }else{
 193                         return "'" + str + "'";
 194                 }
 195         }
 196
 197         private void updateFeatureTerms(Integer termId, ICdmDataSource datasource, IProgressMonitor monitor) throws SQLException {
 198                 if (dtype.equals(Feature.class.getSimpleName())){
 199                         String sqlUpdate = "UPDATE DefinedTermBase SET " +
 200                                 " supportscategoricaldata = " + getBoolean(false, datasource) + ", " +
 201                                 " supportscommontaxonname = " + getBoolean(false, datasource) + ", " +
 202                                 " supportsdistribution = " + getBoolean(false, datasource) + ", " +
 203                                 " supportsindividualassociation = " + getBoolean(false, datasource) + ", " +
 204                                 " supportsquantitativedata = " + getBoolean(false, datasource) + ", " +
 205                                 " supportstaxoninteraction = " + getBoolean(false, datasource) + ", " +
 206                                 " supportstextdata = " + getBoolean(true, datasource) +  " " +
 207                                 " WHERE id = " + termId;
 208                         datasource.executeUpdate(sqlUpdate);
 209                 }
 210         }
 211
 212         private void updateRelationshipTerms(Integer termId, ICdmDataSource datasource, IProgressMonitor monitor) throws SQLException {
 213                 if (dtype.contains("Relationship")){
 214                         String sqlUpdate = "UPDATE DefinedTermBase SET " +
 215                                 " symmetrical = " + getBoolean(symmetric, datasource) + ", " +
 216                                 " transitive = " + getBoolean(transitive, datasource) + " " +
 217                                 " WHERE id = " + termId;
 218                         datasource.executeUpdate(sqlUpdate);
 219                 }
 220         }
 221
 222         private void updateRanks(Integer termId, ICdmDataSource datasource, IProgressMonitor monitor) throws SQLException {
 223                 if (dtype.equals(Rank.class.getSimpleName())){
 224                         String sqlUpdate = "UPDATE DefinedTermBase  " +
 225                                 " SET rankClass = '" + rankClass.getKey() + "'" +
 226                                 " WHERE id = " + termId;
 227                         datasource.executeUpdate(sqlUpdate);
 228                 }
 229         }
 230
 231         public SingleTermUpdater setRankClass(RankClass rankClass) {
 232                 this.rankClass = rankClass;
 233                 return this;
 234         }
 235
 236
 237
 238         /**
 239          * @param datasource
 240          * @param vocId
 241          * @param monitor
 242          * @return
 243          * @throws SQLException
 244          */
 245         private String getOrderIndex(ICdmDataSource datasource, int vocId, IProgressMonitor monitor) throws SQLException {
 246                 ResultSet rs;
 247                 Integer intOrderIndex = null;
 248                 if (uuidAfterTerm == null){
 249                         return "1";
 250                 }
 251                 String sqlOrderIndex = " SELECT orderindex FROM DefinedTermBase WHERE uuid = '"+uuidAfterTerm+"' AND vocabulary_id = "+vocId+"";
 252                 rs = datasource.executeQuery(sqlOrderIndex);
 253                 if (rs.next()){
 254                         intOrderIndex = rs.getInt("orderindex") + 1;
 255
 256                         String sqlUpdateLowerTerms = "UPDATE DefinedTermBase SET orderindex = orderindex + 1 WHERE vocabulary_id = " + vocId+ " AND orderindex >= " + intOrderIndex;
 257                         datasource.executeUpdate(sqlUpdateLowerTerms);
 258                 }else{
 259                         String warning = "The previous term has not been found in vocabulary. Put term to the end";
 260                         monitor.warning(warning);
 261                 }
 262                 if (intOrderIndex == null){
 263                         String sqlMaxOrderIndex = " SELECT max(orderindex) FROM DefinedTermBase WHERE vocabulary_id = " + vocId + "";
 264                         intOrderIndex = (Integer)datasource.getSingleValue(sqlMaxOrderIndex);
 265                         if (intOrderIndex != null){
 266                                 intOrderIndex++;
 267                         }else{
 268                                 String warning = "No term was found in vocabulary or vocabulary does not exist. Use order index '0'.";
 269                                 monitor.warning(warning);
 270                                 intOrderIndex =0;
 271                         }
 272                 }
 273
 274                 return intOrderIndex.toString();
 275         }
 276
 277
 278         private boolean hasReverseRepresentation() {
 279                 return  reverseLabel != null ||  reverseDescription != null ||  reverseAbbrev != null;
 280         }
 281
 282         public SingleTermUpdater setReverseRepresentation(String reverseDescription, String reverseLabel, String reverseAbbrev) {
 283                 this.reverseLabel = reverseLabel;
 284                 this.reverseDescription = reverseDescription;
 285                 this.reverseAbbrev = reverseAbbrev;
 286                 return this;
 287         }
 288
 289         public SingleTermUpdater setSymmetricTransitiv(boolean symmetric, boolean transitive){
 290                 this.symmetric = symmetric;
 291                 this.transitive = transitive;
 292                 return this;
 293         }
 294
 295 }