1
|
/**
|
2
|
* Copyright (C) 2009 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
|
package eu.etaxonomy.cdm.io.stream.mapping;
|
10
|
|
11
|
import java.sql.ResultSet;
|
12
|
import java.sql.SQLException;
|
13
|
import java.util.HashMap;
|
14
|
import java.util.HashSet;
|
15
|
import java.util.Map;
|
16
|
import java.util.Map.Entry;
|
17
|
import java.util.Set;
|
18
|
|
19
|
import org.apache.log4j.Logger;
|
20
|
|
21
|
import eu.etaxonomy.cdm.database.CdmDataSource;
|
22
|
import eu.etaxonomy.cdm.database.CdmPersistentDataSource;
|
23
|
import eu.etaxonomy.cdm.database.DataSourceNotFoundException;
|
24
|
import eu.etaxonomy.cdm.database.ICdmDataSource;
|
25
|
import eu.etaxonomy.cdm.io.stream.mapping.IImportMapping.CdmKey;
|
26
|
import eu.etaxonomy.cdm.model.agent.Person;
|
27
|
import eu.etaxonomy.cdm.model.agent.Team;
|
28
|
import eu.etaxonomy.cdm.model.common.IdentifiableEntity;
|
29
|
import eu.etaxonomy.cdm.model.name.TaxonName;
|
30
|
import eu.etaxonomy.cdm.model.reference.Reference;
|
31
|
import eu.etaxonomy.cdm.model.taxon.Synonym;
|
32
|
import eu.etaxonomy.cdm.model.taxon.Taxon;
|
33
|
|
34
|
/**
|
35
|
* TODO inwork, currently it works with H2
|
36
|
* @author a.mueller
|
37
|
* @since 22.03.2012
|
38
|
*
|
39
|
*/
|
40
|
public class DatabaseMapping implements IImportMapping {
|
41
|
private static final Logger logger = Logger.getLogger(DatabaseMapping.class);
|
42
|
|
43
|
private static final String DATABASE_INTERNAL_IMPORT_MAPPING = "_internalImportMapping";
|
44
|
protected static final String TABLE_IMPORT_MAPPING = "importmapping";
|
45
|
|
46
|
private static final String COL_TASK_ID = "task_id";
|
47
|
|
48
|
private static final String COL_SOURCE_NS = "source_namespace";
|
49
|
|
50
|
private static final String COL_SOURCE_ID = "source_id";
|
51
|
|
52
|
private static final String COL_DEST_NS = "destination_namespace";
|
53
|
|
54
|
private static final String COL_DEST_ID = "destination_id";
|
55
|
|
56
|
private static final int SOURCE_KEY_LENGTH = 255;
|
57
|
|
58
|
|
59
|
private ICdmDataSource datasource;
|
60
|
private final String mappingId;
|
61
|
private final Map<String, Class> shortCuts = new HashMap<>();
|
62
|
private final Map<Class, String> reverseShortCuts = new HashMap<>();
|
63
|
|
64
|
|
65
|
@Override
|
66
|
public void putMapping(String namespace, Integer sourceKey, IdentifiableEntity destinationObject){
|
67
|
putMapping(namespace, String.valueOf(sourceKey), destinationObject);
|
68
|
}
|
69
|
|
70
|
public DatabaseMapping(String mappingId) {
|
71
|
this(mappingId, null);
|
72
|
}
|
73
|
|
74
|
/**
|
75
|
* @param database
|
76
|
*/
|
77
|
public DatabaseMapping(String mappingId, String file) {
|
78
|
super();
|
79
|
initDatasource(file);
|
80
|
this.mappingId = mappingId;
|
81
|
}
|
82
|
|
83
|
@Override
|
84
|
public void putMapping(String namespace, String sourceKey, IdentifiableEntity destinationObject){
|
85
|
CdmKey<IdentifiableEntity<?>> cdmKey = new CdmKey<>(destinationObject);
|
86
|
putMapping(namespace, sourceKey, cdmKey);
|
87
|
}
|
88
|
|
89
|
public void putMapping(String namespace, String sourceKey, CdmKey<IdentifiableEntity<?>> cdmKey) {
|
90
|
try {
|
91
|
String normalizedKey = normalizeKey(sourceKey);
|
92
|
|
93
|
deleteExistingMapping(namespace, sourceKey);
|
94
|
persistNotExistingMapping(namespace, normalizedKey, cdmKey);
|
95
|
} catch (SQLException e) {
|
96
|
throw new RuntimeException(e);
|
97
|
}
|
98
|
}
|
99
|
|
100
|
private int persistNotExistingMapping(String sourceNamespace, String normalizedKey, CdmKey<IdentifiableEntity<?>> cdmKey) throws SQLException {
|
101
|
|
102
|
//cdm namespace
|
103
|
String clazz = getCdmClassStr(cdmKey.clazz);
|
104
|
|
105
|
//insert
|
106
|
String insertMappingSql = " INSERT INTO %s (%s, %s, %s, %s, %s)" +
|
107
|
" VALUES ('%s','%s','%s','%s','%s')";
|
108
|
insertMappingSql = String.format(insertMappingSql,
|
109
|
TABLE_IMPORT_MAPPING, COL_TASK_ID, COL_SOURCE_NS, COL_SOURCE_ID, COL_DEST_NS, COL_DEST_ID,
|
110
|
this.mappingId, sourceNamespace, normalizedKey, clazz, cdmKey.id);
|
111
|
return this.datasource.executeUpdate(insertMappingSql);
|
112
|
}
|
113
|
|
114
|
|
115
|
/**
|
116
|
* @param cdmKey
|
117
|
* @return
|
118
|
*/
|
119
|
private String getCdmClassStr(Class cdmClass) {
|
120
|
String clazz = reverseShortCuts.get(cdmClass);
|
121
|
if (clazz == null){
|
122
|
clazz = cdmClass.getCanonicalName();
|
123
|
}
|
124
|
return clazz;
|
125
|
}
|
126
|
|
127
|
|
128
|
private int deleteExistingMapping(String sourceNamespace, String sourceId) throws SQLException {
|
129
|
String normalizedKey = normalizeKey(sourceId);
|
130
|
String deleteMappingSql = " DELETE FROM %s WHERE %s = '%s' AND %s = '%s' AND %s = '%s'";
|
131
|
deleteMappingSql = String.format(deleteMappingSql,TABLE_IMPORT_MAPPING, COL_TASK_ID, this.mappingId, COL_SOURCE_NS, sourceNamespace, COL_SOURCE_ID, normalizedKey);
|
132
|
return this.datasource.executeUpdate(deleteMappingSql);
|
133
|
}
|
134
|
|
135
|
|
136
|
private int deleteAll() throws SQLException {
|
137
|
String deleteMappingSql = " DELETE FROM %s WHERE %s = '%s' ";
|
138
|
deleteMappingSql = String.format(deleteMappingSql,TABLE_IMPORT_MAPPING, COL_TASK_ID, this.mappingId);
|
139
|
return this.datasource.executeUpdate(deleteMappingSql);
|
140
|
}
|
141
|
|
142
|
public int size() throws SQLException {
|
143
|
String sql = " SELECT count(*) as n FROM %s WHERE %s = '%s' ";
|
144
|
sql = String.format(sql,TABLE_IMPORT_MAPPING, COL_TASK_ID, this.mappingId);
|
145
|
ResultSet rs = this.datasource.executeQuery(sql);
|
146
|
rs.next();
|
147
|
return rs.getInt("n");
|
148
|
|
149
|
}
|
150
|
|
151
|
@Override
|
152
|
public Set<CdmKey> get(String sourceNamespace, String sourceId) {
|
153
|
Set<CdmKey> result = new HashSet<>();
|
154
|
String normalizedKey = normalizeKey(sourceId);
|
155
|
String selectMappingSql = " SELECT %s, %s FROM %s" +
|
156
|
" WHERE %s = '%s' AND %s = '%s' AND %s = '%s' ";
|
157
|
selectMappingSql = String.format(selectMappingSql,
|
158
|
COL_DEST_NS, COL_DEST_ID, TABLE_IMPORT_MAPPING,
|
159
|
COL_TASK_ID, this.mappingId, COL_SOURCE_NS, sourceNamespace,
|
160
|
COL_SOURCE_ID , normalizedKey);
|
161
|
try {
|
162
|
ResultSet rs = this.datasource.executeQuery(selectMappingSql);
|
163
|
while (rs.next()){
|
164
|
String clazzStr = rs.getString(COL_DEST_NS);
|
165
|
Object id = rs.getObject(COL_DEST_ID);
|
166
|
if (id == null){
|
167
|
throw new RuntimeException("Destination id for import mapping is 'null'");
|
168
|
}
|
169
|
|
170
|
Class<?> clazz = getCdmClass(clazzStr);
|
171
|
|
172
|
CdmKey<?> key = new CdmKey<>(clazz, Integer.valueOf(String.valueOf(id)));
|
173
|
result.add(key);
|
174
|
}
|
175
|
} catch (NumberFormatException e) {
|
176
|
throw new RuntimeException(e);
|
177
|
} catch (SQLException e) {
|
178
|
throw new RuntimeException(e);
|
179
|
} catch (ClassNotFoundException e) {
|
180
|
throw new RuntimeException(e);
|
181
|
}
|
182
|
|
183
|
return result;
|
184
|
}
|
185
|
|
186
|
@Override
|
187
|
public boolean exists(String sourceNamespace, String sourceId, Class<?> destinationClass){
|
188
|
String selectMappingSql = " SELECT count(*) as n FROM %s" +
|
189
|
" WHERE %s = '%s' AND %s = '%s' AND %s = '%s' AND %s = '%s' ";
|
190
|
|
191
|
|
192
|
String cdmClass = getCdmClassStr(destinationClass);
|
193
|
String normalizedKey = normalizeKey(sourceId);
|
194
|
selectMappingSql = String.format(selectMappingSql,
|
195
|
TABLE_IMPORT_MAPPING, COL_TASK_ID, this.mappingId,
|
196
|
COL_SOURCE_NS, sourceNamespace, COL_SOURCE_ID , normalizedKey, COL_DEST_NS, cdmClass);
|
197
|
try {
|
198
|
ResultSet rs = this.datasource.executeQuery(selectMappingSql);
|
199
|
rs.next();
|
200
|
int n = rs.getInt("n");
|
201
|
|
202
|
return n > 0;
|
203
|
} catch (SQLException e) {
|
204
|
throw new RuntimeException(e);
|
205
|
}
|
206
|
}
|
207
|
|
208
|
/**
|
209
|
* Normalizes the key coming from the DwCA File.
|
210
|
* This includes handling ' and keys with length > 255
|
211
|
* @param sourceKey
|
212
|
* @return
|
213
|
*/
|
214
|
private String normalizeKey(String key) {
|
215
|
if (key == null){
|
216
|
return null;
|
217
|
}
|
218
|
String result = key.replace("'", "''");
|
219
|
if (result.length() > SOURCE_KEY_LENGTH){
|
220
|
//TODO better use MD5 hash or similar
|
221
|
logger.info("Source key was truncated: " + key);
|
222
|
result = result.substring(0, SOURCE_KEY_LENGTH);
|
223
|
}
|
224
|
return result;
|
225
|
}
|
226
|
|
227
|
@Override
|
228
|
public InMemoryMapping getPartialMapping( Map<String, Set<String>> namespacedSourceKeys) {
|
229
|
InMemoryMapping partialMapping = new InMemoryMapping();
|
230
|
for (Entry<String,Set<String>> entry : namespacedSourceKeys.entrySet()){
|
231
|
String namespace = entry.getKey();
|
232
|
for (String sourceKey : entry.getValue() ){
|
233
|
String normalizedKey = normalizeKey(sourceKey);
|
234
|
Set<CdmKey> destObjects = this.get(namespace, normalizedKey);
|
235
|
for (CdmKey cdmKey : destObjects){
|
236
|
partialMapping.putMapping(namespace, normalizedKey, cdmKey);
|
237
|
}
|
238
|
}
|
239
|
}
|
240
|
return partialMapping;
|
241
|
}
|
242
|
|
243
|
|
244
|
@Override
|
245
|
public void finish() {
|
246
|
try {
|
247
|
int count = size();
|
248
|
deleteAll();
|
249
|
logger.info("Finalize database mapping " + count + ": " + size());
|
250
|
} catch (SQLException e) {
|
251
|
throw new RuntimeException();
|
252
|
}
|
253
|
}
|
254
|
|
255
|
|
256
|
/**
|
257
|
* @param clazzStr
|
258
|
* @return
|
259
|
* @throws ClassNotFoundException
|
260
|
*/
|
261
|
private Class<?> getCdmClass(String clazzStr) throws ClassNotFoundException {
|
262
|
Class<?> clazz = shortCuts.get(clazzStr);
|
263
|
if (clazz == null){
|
264
|
clazz = Class.forName(clazzStr);
|
265
|
}
|
266
|
return clazz;
|
267
|
}
|
268
|
|
269
|
|
270
|
private void initDatasource(String file) {
|
271
|
getDatabase(file);
|
272
|
shortCuts.put("TaxonName", TaxonName.class);
|
273
|
shortCuts.put("Taxon", Taxon.class);
|
274
|
shortCuts.put("Synonym", Synonym.class);
|
275
|
shortCuts.put("Reference", Reference.class);
|
276
|
shortCuts.put("Team", Team.class);
|
277
|
shortCuts.put("Person", Person.class);
|
278
|
//reverse
|
279
|
for (String key :shortCuts.keySet()){
|
280
|
reverseShortCuts.put(shortCuts.get(key), key);
|
281
|
}
|
282
|
}
|
283
|
|
284
|
public ICdmDataSource getDatabase(){
|
285
|
return getDatabase(null);
|
286
|
}
|
287
|
|
288
|
public ICdmDataSource getDatabase(String path){
|
289
|
try {
|
290
|
try {
|
291
|
if (path == null){
|
292
|
datasource = CdmPersistentDataSource.NewInstance(DATABASE_INTERNAL_IMPORT_MAPPING);
|
293
|
}else{
|
294
|
makeDatasource(path);
|
295
|
}
|
296
|
} catch (DataSourceNotFoundException e) {
|
297
|
makeDatasource(path);
|
298
|
CdmPersistentDataSource.save(DATABASE_INTERNAL_IMPORT_MAPPING, datasource);
|
299
|
}
|
300
|
datasource.executeQuery("SELECT * FROM " + TABLE_IMPORT_MAPPING);
|
301
|
} catch (SQLException e) {
|
302
|
//create database structure
|
303
|
try {
|
304
|
String strCreateTable = "CREATE TABLE IF NOT EXISTS %s (";
|
305
|
strCreateTable += "%s nvarchar(36) NOT NULL,";
|
306
|
strCreateTable += "%s nvarchar(100) NOT NULL,";
|
307
|
strCreateTable += "%s nvarchar(" + SOURCE_KEY_LENGTH + ") NOT NULL,";
|
308
|
strCreateTable += "%s nvarchar(100) NOT NULL,";
|
309
|
strCreateTable += "destination_id nvarchar(50) NOT NULL,";
|
310
|
strCreateTable += "PRIMARY KEY (task_id, source_namespace, source_id)";
|
311
|
strCreateTable += ") ";
|
312
|
strCreateTable = String.format(strCreateTable, TABLE_IMPORT_MAPPING, COL_TASK_ID, COL_SOURCE_NS, COL_SOURCE_ID, COL_DEST_NS, COL_DEST_ID);
|
313
|
datasource.executeUpdate(strCreateTable);
|
314
|
logger.warn("Mapping database structure created");
|
315
|
} catch (SQLException e1) {
|
316
|
e1.printStackTrace();
|
317
|
throw new RuntimeException(e1);
|
318
|
}
|
319
|
}
|
320
|
return datasource;
|
321
|
}
|
322
|
|
323
|
/**
|
324
|
* @param path
|
325
|
*/
|
326
|
private void makeDatasource(String path) {
|
327
|
datasource = CdmDataSource.NewH2EmbeddedInstance("_tmpMapping", "a", "b", path);
|
328
|
}
|
329
|
|
330
|
|
331
|
}
|