app-import/src/main/java/eu/etaxonomy/cdm/io/globis/GlobisImportBase.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.globis;
  11
  12 import java.lang.reflect.Method;
  13 import java.sql.ResultSet;
  14 import java.sql.ResultSetMetaData;
  15 import java.sql.SQLException;
  16 import java.sql.Timestamp;
  17 import java.util.HashMap;
  18 import java.util.HashSet;
  19 import java.util.Map;
  20 import java.util.Set;
  21 import java.util.UUID;
  22
  23 import org.apache.log4j.Logger;
  24 import org.joda.time.DateTime;
  25
  26 import eu.etaxonomy.cdm.common.CdmUtils;
  27 import eu.etaxonomy.cdm.io.common.CdmImportBase;
  28 import eu.etaxonomy.cdm.io.common.ICdmIO;
  29 import eu.etaxonomy.cdm.io.common.IImportConfigurator.EDITOR;
  30 import eu.etaxonomy.cdm.io.common.IPartitionedIO;
  31 import eu.etaxonomy.cdm.io.common.ImportHelper;
  32 import eu.etaxonomy.cdm.io.common.ResultSetPartitioner;
  33 import eu.etaxonomy.cdm.io.common.Source;
  34 import eu.etaxonomy.cdm.io.common.mapping.DbImportMapping;
  35 import eu.etaxonomy.cdm.model.common.AnnotatableEntity;
  36 import eu.etaxonomy.cdm.model.common.Annotation;
  37 import eu.etaxonomy.cdm.model.common.AnnotationType;
  38 import eu.etaxonomy.cdm.model.common.CdmBase;
  39 import eu.etaxonomy.cdm.model.common.ExtensionType;
  40 import eu.etaxonomy.cdm.model.common.IdentifiableEntity;
  41 import eu.etaxonomy.cdm.model.common.Language;
  42 import eu.etaxonomy.cdm.model.common.MarkerType;
  43 import eu.etaxonomy.cdm.model.common.User;
  44
  45 /**
  46  * @author a.mueller
  47  * @created 20.03.2008
  48  * @version 1.0
  49  */
  50 public abstract class GlobisImportBase<CDM_BASE extends CdmBase> extends CdmImportBase<GlobisImportConfigurator, GlobisImportState> implements ICdmIO<GlobisImportState>, IPartitionedIO<GlobisImportState> {
  51         private static final Logger logger = Logger.getLogger(GlobisImportBase.class);
  52
  53         public static final UUID ID_IN_SOURCE_EXT_UUID = UUID.fromString("23dac094-e793-40a4-bad9-649fc4fcfd44");
  54
  55         //NAMESPACES
  56
  57         protected static final String AREA_NAMESPACE = "gu";
  58         protected static final String DR_NAMESPACE = "dr";
  59         protected static final String IMAGE_NAMESPACE = "Images";
  60         protected static final String LINKS_NAMESPACE = "Links";
  61         protected static final String NOTES_NAMESPACE = "Notes";
  62         protected static final String LANGUAGE_NAMESPACE = "Language";
  63         protected static final String REFERENCE_NAMESPACE = "Source";
  64         protected static final String SOURCEUSE_NAMESPACE = "tu_sources";
  65         protected static final String TAXON_NAMESPACE = "Taxon";
  66         protected static final String NAME_NAMESPACE = "TaxonName";
  67         protected static final String VERNACULAR_NAMESPACE = "Vernaculars";
  68         protected static final String FEATURE_NAMESPACE = "note.type";
  69         protected static final String EXTENSION_TYPE_NAMESPACE = "ExtensionType";
  70
  71
  72
  73         private String pluralString;
  74         private String dbTableName;
  75         //TODO needed?
  76         private Class cdmTargetClass;
  77
  78
  79
  80         /**
  81          * @param dbTableName
  82          * @param dbTableName2
  83          */
  84         public GlobisImportBase(String pluralString, String dbTableName, Class cdmTargetClass) {
  85                 this.pluralString = pluralString;
  86                 this.dbTableName = dbTableName;
  87                 this.cdmTargetClass = cdmTargetClass;
  88         }
  89
  90         protected void doInvoke(GlobisImportState state){
  91                 logger.info("start make " + getPluralString() + " ...");
  92                 GlobisImportConfigurator config = state.getConfig();
  93                 Source source = config.getSource();
  94
  95                 String strIdQuery = getIdQuery();
  96                 String strRecordQuery = getRecordQuery(config);
  97
  98                 int recordsPerTransaction = config.getRecordsPerTransaction();
  99                 try{
 100                         ResultSetPartitioner partitioner = ResultSetPartitioner.NewInstance(source, strIdQuery, strRecordQuery, recordsPerTransaction);
 101                         while (partitioner.nextPartition()){
 102                                 partitioner.doPartition(this, state);
 103                         }
 104                 } catch (SQLException e) {
 105                         logger.error("SQLException:" +  e);
 106                         state.setUnsuccessfull();
 107                 }
 108
 109                 logger.info("end make " + getPluralString() + " ... " + getSuccessString(true));
 110                 return;
 111         }
 112
 113         public boolean doPartition(ResultSetPartitioner partitioner, GlobisImportState state) {
 114                 boolean success = true ;
 115                 Set objectsToSave = new HashSet();
 116
 117                 DbImportMapping<?, ?> mapping = getMapping();
 118                 mapping.initialize(state, cdmTargetClass);
 119
 120                 ResultSet rs = partitioner.getResultSet();
 121                 try{
 122                         while (rs.next()){
 123                                 success &= mapping.invoke(rs,objectsToSave);
 124                         }
 125                 } catch (SQLException e) {
 126                         logger.error("SQLException:" +  e);
 127                         return false;
 128                 }
 129
 130                 partitioner.startDoSave();
 131                 getCommonService().save(objectsToSave);
 132                 return success;
 133         }
 134
 135
 136
 137         /**
 138          * @return
 139          */
 140         protected abstract DbImportMapping<?, ?> getMapping();
 141
 142         /**
 143          * @return
 144          */
 145         protected abstract String getRecordQuery(GlobisImportConfigurator config);
 146
 147         /**
 148          * @return
 149          */
 150         protected String getIdQuery(){
 151                 String result = " SELECT id FROM " + getTableName();
 152                 return result;
 153         }
 154
 155         /* (non-Javadoc)
 156          * @see eu.etaxonomy.cdm.io.berlinModel.in.IPartitionedIO#getPluralString()
 157          */
 158         public String getPluralString(){
 159                 return pluralString;
 160         }
 161
 162         /**
 163          * @return
 164          */
 165         protected String getTableName(){
 166                 return this.dbTableName;
 167         }
 168
 169         protected boolean doIdCreatedUpdatedNotes(GlobisImportState state, IdentifiableEntity identifiableEntity, ResultSet rs, long id, String namespace)
 170                         throws SQLException{
 171                 boolean success = true;
 172                 //id
 173                 success &= ImportHelper.setOriginalSource(identifiableEntity, state.getConfig().getSourceReference(), id, namespace);
 174                 //createdUpdateNotes
 175                 success &= doCreatedUpdatedNotes(state, identifiableEntity, rs, namespace);
 176                 return success;
 177         }
 178
 179
 180         protected boolean doCreatedUpdatedNotes(GlobisImportState state, AnnotatableEntity annotatableEntity, ResultSet rs, String namespace)
 181                         throws SQLException{
 182
 183                 GlobisImportConfigurator config = state.getConfig();
 184                 Object createdWhen = rs.getObject("Created_When");
 185                 String createdWho = rs.getString("Created_Who");
 186                 Object updatedWhen = null;
 187                 String updatedWho = null;
 188                 try {
 189                         updatedWhen = rs.getObject("Updated_When");
 190                         updatedWho = rs.getString("Updated_who");
 191                 } catch (SQLException e) {
 192                         //Table "Name" has no updated when/who
 193                 }
 194                 String notes = rs.getString("notes");
 195
 196                 boolean success  = true;
 197
 198                 //Created When, Who, Updated When Who
 199                 if (config.getEditor() == null || config.getEditor().equals(EDITOR.NO_EDITORS)){
 200                         //do nothing
 201                 }else if (config.getEditor().equals(EDITOR.EDITOR_AS_ANNOTATION)){
 202                         String createdAnnotationString = "Berlin Model record was created By: " + String.valueOf(createdWho) + " (" + String.valueOf(createdWhen) + ") ";
 203                         if (updatedWhen != null && updatedWho != null){
 204                                 createdAnnotationString += " and updated By: " + String.valueOf(updatedWho) + " (" + String.valueOf(updatedWhen) + ")";
 205                         }
 206                         Annotation annotation = Annotation.NewInstance(createdAnnotationString, Language.DEFAULT());
 207                         annotation.setCommentator(config.getCommentator());
 208                         annotation.setAnnotationType(AnnotationType.TECHNICAL());
 209                         annotatableEntity.addAnnotation(annotation);
 210                 }else if (config.getEditor().equals(EDITOR.EDITOR_AS_EDITOR)){
 211                         User creator = getUser(createdWho, state);
 212                         User updator = getUser(updatedWho, state);
 213                         DateTime created = getDateTime(createdWhen);
 214                         DateTime updated = getDateTime(updatedWhen);
 215                         annotatableEntity.setCreatedBy(creator);
 216                         annotatableEntity.setUpdatedBy(updator);
 217                         annotatableEntity.setCreated(created);
 218                         annotatableEntity.setUpdated(updated);
 219                 }else {
 220                         logger.warn("Editor type not yet implemented: " + config.getEditor());
 221                 }
 222
 223
 224                 //notes
 225                 if (CdmUtils.isNotEmpty(notes)){
 226                         String notesString = String.valueOf(notes);
 227                         if (notesString.length() > 65530 ){
 228                                 notesString = notesString.substring(0, 65530) + "...";
 229                                 logger.warn("Notes string is longer than 65530 and was truncated: " + annotatableEntity);
 230                         }
 231                         Annotation notesAnnotation = Annotation.NewInstance(notesString, null);
 232                         //notesAnnotation.setAnnotationType(AnnotationType.EDITORIAL());
 233                         //notes.setCommentator(bmiConfig.getCommentator());
 234                         annotatableEntity.addAnnotation(notesAnnotation);
 235                 }
 236                 return success;
 237         }
 238
 239         private User getUser(String userString, GlobisImportState state){
 240                 if (CdmUtils.isEmpty(userString)){
 241                         return null;
 242                 }
 243                 userString = userString.trim();
 244
 245                 User user = state.getUser(userString);
 246                 if (user == null){
 247                         user = getTransformedUser(userString,state);
 248                 }
 249                 if (user == null){
 250                         user = makeNewUser(userString, state);
 251                 }
 252                 if (user == null){
 253                         logger.warn("User is null");
 254                 }
 255                 return user;
 256         }
 257
 258         private User getTransformedUser(String userString, GlobisImportState state){
 259                 Method method = state.getConfig().getUserTransformationMethod();
 260                 if (method == null){
 261                         return null;
 262                 }
 263                 try {
 264                         userString = (String)state.getConfig().getUserTransformationMethod().invoke(null, userString);
 265                 } catch (Exception e) {
 266                         logger.warn("Error when trying to transform userString " +  userString + ". No transformation done.");
 267                 }
 268                 User user = state.getUser(userString);
 269                 return user;
 270         }
 271
 272         private User makeNewUser(String userString, GlobisImportState state){
 273                 String pwd = getPassword();
 274                 User user = User.NewInstance(userString, pwd);
 275                 state.putUser(userString, user);
 276                 getUserService().save(user);
 277                 logger.info("Added new user: " + userString);
 278                 return user;
 279         }
 280
 281         private String getPassword(){
 282                 String result = UUID.randomUUID().toString();
 283                 return result;
 284         }
 285
 286         private DateTime getDateTime(Object timeString){
 287                 if (timeString == null){
 288                         return null;
 289                 }
 290                 DateTime dateTime = null;
 291                 if (timeString instanceof Timestamp){
 292                         Timestamp timestamp = (Timestamp)timeString;
 293                         dateTime = new DateTime(timestamp);
 294                 }else{
 295                         logger.warn("time ("+timeString+") is not a timestamp. Datetime set to current date. ");
 296                         dateTime = new DateTime();
 297                 }
 298                 return dateTime;
 299         }
 300
 301         protected boolean resultSetHasColumn(ResultSet rs, String columnName){
 302                 try {
 303                         ResultSetMetaData metaData = rs.getMetaData();
 304                         for (int i = 0; i < metaData.getColumnCount(); i++){
 305                                 if (metaData.getColumnName(i + 1).equalsIgnoreCase(columnName)){
 306                                         return true;
 307                                 }
 308                         }
 309                         return false;
 310                 } catch (SQLException e) {
 311             logger.warn("Exception in resultSetHasColumn");
 312             return false;
 313                 }
 314         }
 315
 316         protected boolean checkSqlServerColumnExists(Source source, String tableName, String columnName){
 317                 String strQuery = "SELECT  Count(t.id) as n " +
 318                                 " FROM sysobjects AS t " +
 319                                 " INNER JOIN syscolumns AS c ON t.id = c.id " +
 320                                 " WHERE (t.xtype = 'U') AND " +
 321                                 " (t.name = '" + tableName + "') AND " +
 322                                 " (c.name = '" + columnName + "')";
 323                 ResultSet rs = source.getResultSet(strQuery) ;
 324                 int n;
 325                 try {
 326                         rs.next();
 327                         n = rs.getInt("n");
 328                         return n>0;
 329                 } catch (SQLException e) {
 330                         e.printStackTrace();
 331                         return false;
 332                 }
 333
 334         }
 335
 336         /**
 337          * Returns a map that holds all values of a ResultSet. This is needed if a value needs to
 338          * be accessed twice
 339          * @param rs
 340          * @return
 341          * @throws SQLException
 342          */
 343         protected Map<String, Object> getValueMap(ResultSet rs) throws SQLException{
 344                 try{
 345                         Map<String, Object> valueMap = new HashMap<String, Object>();
 346                         int colCount = rs.getMetaData().getColumnCount();
 347                         for (int c = 0; c < colCount ; c++){
 348                                 Object value = rs.getObject(c+1);
 349                                 String label = rs.getMetaData().getColumnLabel(c+1).toLowerCase();
 350                                 if (value != null && ! CdmUtils.Nz(value.toString()).trim().equals("")){
 351                                         valueMap.put(label, value);
 352                                 }
 353                         }
 354                         return valueMap;
 355                 }catch(SQLException e){
 356                         throw e;
 357                 }
 358         }
 359
 360         protected ExtensionType getExtensionType(UUID uuid, String label, String text, String labelAbbrev){
 361                 ExtensionType extensionType = (ExtensionType)getTermService().find(uuid);
 362                 if (extensionType == null){
 363                         extensionType = ExtensionType.NewInstance(text, label, labelAbbrev);
 364                         extensionType.setUuid(uuid);
 365                         getTermService().save(extensionType);
 366                 }
 367                 return extensionType;
 368         }
 369
 370         protected MarkerType getMarkerType(UUID uuid, String label, String text, String labelAbbrev){
 371                 MarkerType markerType = (MarkerType)getTermService().find(uuid);
 372                 if (markerType == null){
 373                         markerType = MarkerType.NewInstance(label, text, labelAbbrev);
 374                         markerType.setUuid(uuid);
 375                         getTermService().save(markerType);
 376                 }
 377                 return markerType;
 378         }
 379
 380
 381         /**
 382          * Reads a foreign key field from the result set and adds its value to the idSet.
 383          * @param rs
 384          * @param teamIdSet
 385          * @throws SQLException
 386          */
 387         protected void handleForeignKey(ResultSet rs, Set<String> idSet, String attributeName)
 388                         throws SQLException {
 389                 Object idObj = rs.getObject(attributeName);
 390                 if (idObj != null){
 391                         String id  = String.valueOf(idObj);
 392                         idSet.add(id);
 393                 }
 394         }
 395
 396         /**
 397          * Returns true if i is a multiple of recordsPerTransaction
 398          * @param i
 399          * @param recordsPerTransaction
 400          * @return
 401          */
 402         protected boolean loopNeedsHandling(int i, int recordsPerLoop) {
 403                 startTransaction();
 404                 return (i % recordsPerLoop) == 0;
 405         }
 406
 407         protected void doLogPerLoop(int count, int recordsPerLog, String pluralString){
 408                 if ((count % recordsPerLog ) == 0 && count!= 0 ){ logger.info(pluralString + " handled: " + (count));}
 409         }
 410
 411
 412
 413
 414 }