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