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.IProgressMonitor;
  19 import eu.etaxonomy.cdm.database.ICdmDataSource;
  20 import eu.etaxonomy.cdm.model.description.Feature;
  21
  22 /**
  23  * @author a.mueller
  24  * @date 10.09.2010
  25  *
  26  */
  27 public class SingleTermUpdater extends SchemaUpdaterStepBase {
  28         @SuppressWarnings("unused")
  29         private static final Logger logger = Logger.getLogger(SingleTermUpdater.class);
  30
  31         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){
  32                 return new SingleTermUpdater(stepName, uuidTerm, description, label, abbrev, dtype, uuidVocabulary, uuidLanguage, isOrdered, uuidAfterTerm);
  33         }
  34
  35         private UUID uuidTerm ;
  36         private String description;
  37         private String label;
  38         private String abbrev;
  39         private String dtype;
  40         private UUID uuidVocabulary;
  41         private boolean isOrdered;
  42         private UUID uuidAfterTerm;
  43         private UUID uuidLanguage;
  44
  45
  46         private SingleTermUpdater(String stepName, UUID uuidTerm, String description, String label, String abbrev, String dtype, UUID uuidVocabulary, UUID uuidLanguage, boolean isOrdered, UUID uuidAfterTerm) {
  47                 super(stepName);
  48                 this.abbrev = abbrev;
  49                 this.description = description;
  50                 this.dtype = dtype;
  51                 this.label = label;
  52                 this.isOrdered = isOrdered;
  53                 this.uuidTerm = uuidTerm;
  54                 this.uuidVocabulary = uuidVocabulary;
  55                 this.uuidAfterTerm = uuidAfterTerm;
  56                 this.uuidLanguage = uuidLanguage;
  57         }
  58
  59
  60
  61         public Integer invoke(ICdmDataSource datasource, IProgressMonitor monitor) throws SQLException{
  62                 String sqlCheckTermExists = " SELECT count(*) as n FROM DefinedTermBase WHERE uuid = '" + uuidTerm + "'";
  63                 Long n = (Long)datasource.getSingleValue(sqlCheckTermExists);
  64                 if (n != 0){
  65                         monitor.warning("Term already exists: " + label + "(" + uuidTerm + ")");
  66                         return null;
  67                 }
  68
  69                 //vocabulary id
  70                 int vocId;
  71                 String sqlVocId = " SELECT id FROM TermVocabulary WHERE uuid = '" + uuidVocabulary + "'";
  72                 ResultSet rs = datasource.executeQuery(sqlVocId);
  73                 if (rs.next()){
  74                         vocId = rs.getInt("id");
  75                 }else{
  76                         String warning = "Vocabulary ( "+ uuidVocabulary +" ) for term does not exist!";
  77                         monitor.warning(warning);
  78                         return null;
  79                 }
  80
  81                 Integer termId;
  82                 String sqlMaxId = " SELECT max(id)+1 as maxId FROM DefinedTermBase";
  83                 rs = datasource.executeQuery(sqlMaxId);
  84                 if (rs.next()){
  85                         termId = rs.getInt("maxId");
  86                 }else{
  87                         String warning = "No defined terms do exist yet. Can't update terms!";
  88                         monitor.warning(warning);
  89                         return null;
  90                 }
  91
  92                 String id = Integer.toString(termId);
  93                 String created = "2010-09-16 10:15:00";
  94                 String defaultColor = "null";
  95                 String protectedTitleCache = getBoolean(false, datasource);
  96                 String orderIndex;
  97                 if (isOrdered){
  98                         orderIndex = getOrderIndex(datasource, vocId, monitor);
  99                 }else{
 100                         orderIndex = "null";
 101                 }
 102                 String titleCache = label != null ? label : (abbrev != null ? abbrev : description );
 103                 String sqlInsertTerm = " INSERT INTO DefinedTermBase (DTYPE, id, uuid, created, protectedtitlecache, titleCache, orderindex, defaultcolor, vocabulary_id)" +
 104                                 "VALUES ('" + dtype + "', " + id + ", '" + uuidTerm + "', '" + created + "', " + protectedTitleCache + ", '" + titleCache + "', " + orderIndex + ", " + defaultColor + ", " + vocId + ")";
 105                 datasource.executeUpdate(sqlInsertTerm);
 106
 107                 updateFeatureTerms(termId, datasource, monitor);
 108
 109 //
 110 //              INSERT INTO DefinedTermBase (DTYPE, id, uuid, created, protectedtitlecache, titleCache, orderindex, defaultcolor, vocabulary_id)
 111 //              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
 112 //              FROM DefinedTermBase ;
 113 //
 114
 115                 //language id
 116                 int langId;
 117                 String sqlLangId = " SELECT id FROM DefinedTermBase WHERE uuid = '" + uuidLanguage + "'";
 118                 rs = datasource.executeQuery(sqlLangId);
 119                 if (rs.next()){
 120                         langId = rs.getInt("id");
 121                 }else{
 122                         String warning = "Term for default language (English) not  does not exist!";
 123                         monitor.warning(warning);
 124                         return null;
 125                 }
 126
 127                 //representation
 128                 int repId;
 129                 sqlMaxId = " SELECT max(id)+1 as maxId FROM Representation";
 130                 rs = datasource.executeQuery(sqlMaxId);
 131                 if (rs.next()){
 132                         repId = rs.getInt("maxId");
 133                 }else{
 134                         String warning = "No representations do exist yet. Can't update terms!";
 135                         monitor.warning(warning);
 136                         return null;
 137                 }
 138
 139                 UUID uuidRepresentation = UUID.randomUUID();
 140                 String sqlInsertRepresentation = " INSERT INTO Representation (id, created, uuid, text, abbreviatedlabel, label, language_id) " +
 141                                 "VALUES (" + repId + ", '" + created + "', '" + uuidRepresentation + "', '" + description +  "', '" + label +  "',  '" + abbrev +  "', " + langId + ")";
 142
 143                 datasource.executeUpdate(sqlInsertRepresentation);
 144
 145                 String sqlInsertMN = "INSERT INTO DefinedTermBase_Representation (DefinedTermBase_id, representations_id) " +
 146                                 " VALUES ("+ termId +"," +repId+ " )";
 147
 148                 datasource.executeUpdate(sqlInsertMN);
 149
 150                 return termId;
 151         }
 152
 153
 154
 155         private void updateFeatureTerms(Integer termId, ICdmDataSource datasource, IProgressMonitor monitor) {
 156                 if (dtype.equals(Feature.class.getSimpleName())){
 157                         String sqlUpdate = "UPDATE DefinedTermBase SET " +
 158                                 " supportscategoricaldata = " + getBoolean(false, datasource) + ", " +
 159                                 " supportscommontaxonname = " + getBoolean(false, datasource) + ", " +
 160                                 " supportsdistribution = " + getBoolean(false, datasource) + ", " +
 161                                 " supportsindividualassociation = " + getBoolean(false, datasource) + ", " +
 162                                 " supportsquantitativedata = " + getBoolean(false, datasource) + ", " +
 163                                 " supportstaxoninteraction = " + getBoolean(false, datasource) + ", " +
 164                                 " supportstextdata = " + getBoolean(true, datasource) +  " " +
 165                                 " WHERE id = " + termId;
 166                         datasource.executeUpdate(sqlUpdate);
 167                 }
 168         }
 169
 170
 171
 172
 173         /**
 174          * @param datasource
 175          * @param vocId
 176          * @param monitor
 177          * @return
 178          * @throws SQLException
 179          */
 180         private String getOrderIndex(ICdmDataSource datasource, int vocId, IProgressMonitor monitor) throws SQLException {
 181                 ResultSet rs;
 182                 Integer intOrderIndex = null;
 183                 String sqlOrderIndex = " SELECT orderindex FROM DefinedTermBase WHERE uuid = '"+uuidAfterTerm+"' AND vocabulary_id = "+vocId+"";
 184                 rs = datasource.executeQuery(sqlOrderIndex);
 185                 if (rs.next()){
 186                         intOrderIndex = rs.getInt("orderindex") + 1;
 187
 188                         String sqlUpdateLowerTerms = "UPDATE DefinedTermBase SET orderindex = orderindex + 1 WHERE vocabulary_id = " + vocId+ " AND orderindex >= " + intOrderIndex;
 189                         datasource.executeUpdate(sqlUpdateLowerTerms);
 190                 }else{
 191                         String warning = "The previous term has not been found in vocabulary. Put term to the end";
 192                         monitor.warning(warning);
 193                         return "null";
 194                 }
 195                 if (intOrderIndex == null){
 196                         String sqlMaxOrderIndex = " SELECT max(orderindex) FROM DefinedTermBase WHERE vocabulary_id = " + vocId + "";
 197                         intOrderIndex = (Integer)datasource.getSingleValue(sqlMaxOrderIndex);
 198                         if (intOrderIndex != null){
 199                                 intOrderIndex++;
 200                         }else{
 201                                 String warning = "No term was found in vocabulary or vocabulary does not exist. Use order index '0'.";
 202                                 monitor.warning(warning);
 203                                 return "0";
 204                         }
 205                 }
 206
 207                 return intOrderIndex.toString();
 208 //                      -- absence term max orderindex
 209 //                      SELECT (@maxAbsenceOrderIndex := max(orderindex)) AS b FROM DefinedTermBase WHERE DTYPE = 'AbsenceTerm';
 210 //
 211 //                      -- native reported in error
 212 //                      SELECT (@presenceOrderIndex := orderindex) AS a FROM DefinedTermBase WHERE uuid = '4ba212ef-041e-418d-9d43-2ebb191b61d8';
 213 //                      UPDATE DefinedTermBase SET uuid = '61cee840-801e-41d8-bead-015ad866c2f1', DTYPE = 'AbsenceTerm', vocabulary_id = 18, orderindex = @maxAbsenceOrderIndex + 1 WHERE uuid = '4ba212ef-041e-418d-9d43-2ebb191b61d8';
 214 //                      UPDATE DefinedTermBase SET orderindex = orderindex -1 WHERE DTYPE = 'PresenceTerm' AND orderindex > @presenceOrderIndex ;
 215         }
 216
 217 }