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