|
1
|
package eu.dnetlib.enabling.database.utils;
|
|
2
|
|
|
3
|
import java.io.StringReader;
|
|
4
|
import java.sql.Array;
|
|
5
|
import java.sql.Connection;
|
|
6
|
import java.sql.Date;
|
|
7
|
import java.sql.PreparedStatement;
|
|
8
|
import java.sql.ResultSet;
|
|
9
|
import java.sql.ResultSetMetaData;
|
|
10
|
import java.sql.SQLException;
|
|
11
|
import java.text.ParseException;
|
|
12
|
import java.text.SimpleDateFormat;
|
|
13
|
import java.util.ArrayList;
|
|
14
|
import java.util.HashMap;
|
|
15
|
import java.util.List;
|
|
16
|
import java.util.Map;
|
|
17
|
import java.util.Set;
|
|
18
|
import java.util.concurrent.ArrayBlockingQueue;
|
|
19
|
import java.util.concurrent.BlockingQueue;
|
|
20
|
import java.util.concurrent.Executors;
|
|
21
|
import java.util.concurrent.TimeUnit;
|
|
22
|
import java.util.concurrent.atomic.AtomicReference;
|
|
23
|
import java.util.regex.Pattern;
|
|
24
|
|
|
25
|
import javax.sql.DataSource;
|
|
26
|
|
|
27
|
import org.apache.commons.lang.BooleanUtils;
|
|
28
|
import org.apache.commons.logging.Log;
|
|
29
|
import org.apache.commons.logging.LogFactory;
|
|
30
|
import org.apache.velocity.app.VelocityEngine;
|
|
31
|
import org.dom4j.Document;
|
|
32
|
import org.dom4j.DocumentHelper;
|
|
33
|
import org.dom4j.Element;
|
|
34
|
import org.dom4j.Node;
|
|
35
|
import org.dom4j.io.SAXReader;
|
|
36
|
import org.joda.time.DateTime;
|
|
37
|
import org.joda.time.format.ISODateTimeFormat;
|
|
38
|
import org.springframework.beans.factory.annotation.Required;
|
|
39
|
import org.springframework.dao.DataAccessException;
|
|
40
|
import org.springframework.jdbc.core.JdbcTemplate;
|
|
41
|
import org.springframework.jdbc.support.rowset.SqlRowSet;
|
|
42
|
import org.springframework.transaction.support.TransactionTemplate;
|
|
43
|
import org.springframework.ui.velocity.VelocityEngineUtils;
|
|
44
|
|
|
45
|
import com.google.common.collect.Lists;
|
|
46
|
import com.google.common.collect.Queues;
|
|
47
|
import com.google.common.collect.Sets;
|
|
48
|
|
|
49
|
import eu.dnetlib.enabling.database.DataSourceFactory;
|
|
50
|
import eu.dnetlib.enabling.database.TransactionTemplateFactory;
|
|
51
|
import eu.dnetlib.enabling.database.objects.DnetDatabase;
|
|
52
|
import eu.dnetlib.enabling.database.rmi.DatabaseException;
|
|
53
|
import eu.dnetlib.miscutils.datetime.DateUtils;
|
|
54
|
import eu.dnetlib.miscutils.functional.string.Sanitizer;
|
|
55
|
|
|
56
|
public class DatabaseUtils {
|
|
57
|
|
|
58
|
public static final String DNET_RESOURCE_ID_FIELD = "_dnet_resource_identifier_";
|
|
59
|
public static final int BLOCKING_QUEUE_TIMEOUT = 300;
|
|
60
|
private static final String SQL_DATE_FORMAT = "yyyy-MM-dd";
|
|
61
|
private static final Log log = LogFactory.getLog(DatabaseUtils.class); // NOPMD by marko on 11/24/08 5:02 PM
|
|
62
|
private static final int BLOCKING_QUEUE_SIZE = 200;
|
|
63
|
private static final Set<String> TRUE_VALUES = Sets.newHashSet("true", "t", "yes", "y", "vero", "v");
|
|
64
|
private static final Set<String> FALSE_VALUES = Sets.newHashSet("false", "f", "no", "n", "falso");
|
|
65
|
private DataSourceFactory dataSourceFactory;
|
|
66
|
private JdbcTemplateFactory jdbcTemplateFactory;
|
|
67
|
private TransactionTemplateFactory transactionTemplateFactory;
|
|
68
|
private String defaultDB;
|
|
69
|
private VelocityEngine velocityEngine;
|
|
70
|
private String dbPrefix;
|
|
71
|
private int numbersOfRecordsForTransaction;
|
|
72
|
private int fetchSize = 100;
|
|
73
|
|
|
74
|
public List<String> listCommonDBTables(final String database) throws DatabaseException {
|
|
75
|
final String query =
|
|
76
|
"SELECT table_name FROM information_schema.tables " + "WHERE table_schema = 'public' " + "AND table_type != 'VIEW' "
|
|
77
|
+ "AND table_name NOT LIKE '%_log'";
|
|
78
|
return getTypedListFromSql(database, query, String.class);
|
|
79
|
}
|
|
80
|
|
|
81
|
public List<String> listCommonDBViews(final String database) throws DatabaseException {
|
|
82
|
final String query =
|
|
83
|
"SELECT table_name FROM information_schema.tables " + "WHERE table_schema = 'public' " + "AND table_type = 'VIEW' "
|
|
84
|
+ "AND table_name NOT LIKE '%_log'";
|
|
85
|
return getTypedListFromSql(database, query, String.class);
|
|
86
|
}
|
|
87
|
|
|
88
|
public Map<String, TableDates> getTableDatesForDB(final String db) throws DatabaseException {
|
|
89
|
final Map<String, TableDates> res = new HashMap<>();
|
|
90
|
|
|
91
|
for (final String table : listCommonDBTables(db)) {
|
|
92
|
try {
|
|
93
|
final TableDates dates = new TableDates();
|
|
94
|
|
|
95
|
final String query =
|
|
96
|
"select lastinsert, lastupdate, lastdelete from " + "(select max(date) as lastinsert from " + table
|
|
97
|
+ "_log where operation='insert') as t1, " + "(select max(date) as lastupdate from " + table
|
|
98
|
+ "_log where operation='update') as t2, " + "(select max(date) as lastdelete from " + table
|
|
99
|
+ "_log where operation='delete') as t3";
|
|
100
|
|
|
101
|
final SqlRowSet srs = executeSql(db, query, SqlRowSet.class);
|
|
102
|
if (srs.next()) {
|
|
103
|
dates.setLastInsert(srs.getDate("lastinsert"));
|
|
104
|
dates.setLastUpdate(srs.getDate("lastupdate"));
|
|
105
|
dates.setLastDelete(srs.getDate("lastdelete"));
|
|
106
|
}
|
|
107
|
res.put(table, dates);
|
|
108
|
} catch (final Exception e) {
|
|
109
|
log.warn("Error obtaing dates for table " + table, e);
|
|
110
|
}
|
|
111
|
}
|
|
112
|
return res;
|
|
113
|
}
|
|
114
|
|
|
115
|
public List<DnetDatabase> listAllDatabases() throws DatabaseException {
|
|
116
|
final String query =
|
|
117
|
"SELECT d.datname AS db, COALESCE(dsc.description,'')='isManaged' AS managed FROM pg_database d LEFT OUTER JOIN pg_shdescription dsc ON (d.oid = dsc.objoid) WHERE d.datname LIKE '"
|
|
118
|
+ dbPrefix + "%' ORDER BY d.datname DESC";
|
|
119
|
final JdbcTemplate jdbcTemplate = getJdbcTemplate(defaultDB);
|
|
120
|
|
|
121
|
final List<DnetDatabase> list = Lists.newArrayList();
|
|
122
|
for (final Map<String, Object> map : jdbcTemplate.queryForList(query)) {
|
|
123
|
list.add(new DnetDatabase(map.get("db").toString(), Boolean.parseBoolean(map.get("managed").toString())));
|
|
124
|
}
|
|
125
|
return list;
|
|
126
|
|
|
127
|
}
|
|
128
|
|
|
129
|
public <T> List<T> getTypedListFromSql(final String dbName, final String query, final Class<T> clazz) throws DatabaseException {
|
|
130
|
try {
|
|
131
|
final List<T> list = new ArrayList<>();
|
|
132
|
for (final Object obj : getJdbcTemplate(dbName).queryForList(query, clazz)) {
|
|
133
|
list.add(clazz.cast(obj));
|
|
134
|
}
|
|
135
|
return list;
|
|
136
|
} catch (final DataAccessException e) {
|
|
137
|
throw new DatabaseException(e);
|
|
138
|
}
|
|
139
|
}
|
|
140
|
|
|
141
|
public List<String> getSimpleListFromSql(final String dbName, final String query) throws DatabaseException {
|
|
142
|
|
|
143
|
final JdbcTemplate jdbcTemplate = getJdbcTemplate(dbName);
|
|
144
|
|
|
145
|
try {
|
|
146
|
final List<String> list = new ArrayList<>();
|
|
147
|
for (final Object obj : jdbcTemplate.queryForList(query)) {
|
|
148
|
list.add(obj.toString());
|
|
149
|
}
|
|
150
|
return list;
|
|
151
|
} catch (final DataAccessException e) {
|
|
152
|
throw new DatabaseException(e);
|
|
153
|
}
|
|
154
|
}
|
|
155
|
|
|
156
|
public void executeSql(final String db, final String query) throws DatabaseException {
|
|
157
|
executeSql(db, query, Void.class);
|
|
158
|
}
|
|
159
|
|
|
160
|
@SuppressWarnings("unchecked")
|
|
161
|
public <T> T executeSql(final String dbName, final String query, final Class<T> clazz) throws DatabaseException {
|
|
162
|
|
|
163
|
if (clazz == BlockingQueue.class) {
|
|
164
|
log.debug("Creating Queue");
|
|
165
|
|
|
166
|
final ArrayBlockingQueue<Document> queue = Queues.newArrayBlockingQueue(BLOCKING_QUEUE_SIZE);
|
|
167
|
Executors.newSingleThreadExecutor().submit(() -> {
|
|
168
|
final DataSource ds = dataSourceFactory.createDataSource(dbName);
|
|
169
|
try (final Connection con = getConnection(ds);
|
|
170
|
final PreparedStatement stm = getStm(query, con);
|
|
171
|
final ResultSet rs = stm.executeQuery()) {
|
|
172
|
|
|
173
|
rs.setFetchSize(getFetchSize());
|
|
174
|
boolean timeout = false;
|
|
175
|
log.info(String.format("[Thread Id %s] starting to populate queue", Thread.currentThread().getId()));
|
|
176
|
while (rs.next()) {
|
|
177
|
final ResultSetMetaData md = rs.getMetaData();
|
|
178
|
final Map<String, Object> row = new HashMap<>();
|
|
179
|
for (int i = 1; i <= md.getColumnCount(); i++) {
|
|
180
|
row.put(md.getColumnName(i), rs.getObject(i));
|
|
181
|
}
|
|
182
|
if (!enqueue(queue, row)) {
|
|
183
|
timeout = true;
|
|
184
|
break;
|
|
185
|
}
|
|
186
|
}
|
|
187
|
if (timeout) {
|
|
188
|
log.warn(String.format("[Thread Id %s] queue full, consumer did not consume for %s seconds, I give up", Thread.currentThread()
|
|
189
|
.getId(), BLOCKING_QUEUE_TIMEOUT));
|
|
190
|
return;
|
|
191
|
}
|
|
192
|
// An empty Map indicates the end of the resultset
|
|
193
|
enqueue(queue, new HashMap<>());
|
|
194
|
} catch (SQLException | DatabaseException e) {
|
|
195
|
throw new RuntimeException(e);
|
|
196
|
}
|
|
197
|
});
|
|
198
|
|
|
199
|
log.debug("Returned Queue");
|
|
200
|
|
|
201
|
return (T) queue;
|
|
202
|
}
|
|
203
|
|
|
204
|
final JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(dbName);
|
|
205
|
if (clazz == Integer.class) {
|
|
206
|
return (T) jdbcTemplate.queryForObject(query, Integer.class);
|
|
207
|
} else if (clazz == List.class) {
|
|
208
|
return (T) jdbcTemplate.queryForList(query);
|
|
209
|
} else if (clazz == Map.class) {
|
|
210
|
return (T) jdbcTemplate.queryForMap(query);
|
|
211
|
} else if (clazz == SqlRowSet.class) {
|
|
212
|
return (T) jdbcTemplate.queryForRowSet(query);
|
|
213
|
} else {
|
|
214
|
jdbcTemplate.update(query);
|
|
215
|
return null;
|
|
216
|
}
|
|
217
|
}
|
|
218
|
|
|
219
|
private boolean enqueue(final ArrayBlockingQueue<Document> q, final Map<String, Object> row) throws DatabaseException {
|
|
220
|
try {
|
|
221
|
return q.offer(rowToDocument(row), BLOCKING_QUEUE_TIMEOUT, TimeUnit.SECONDS);
|
|
222
|
} catch (final InterruptedException e) {
|
|
223
|
log.error("Error putting element in queue");
|
|
224
|
throw new RuntimeException(e);
|
|
225
|
}
|
|
226
|
}
|
|
227
|
|
|
228
|
private PreparedStatement getStm(final String query, final Connection con) throws SQLException {
|
|
229
|
final PreparedStatement stm = con.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY);
|
|
230
|
stm.setFetchSize(getFetchSize());
|
|
231
|
return stm;
|
|
232
|
}
|
|
233
|
|
|
234
|
private Connection getConnection(final DataSource dataSource) throws SQLException {
|
|
235
|
final Connection conn = dataSource.getConnection();
|
|
236
|
conn.setAutoCommit(false);
|
|
237
|
return conn;
|
|
238
|
}
|
|
239
|
|
|
240
|
public boolean contains(final String db, final String table, final String column, final String value) throws DatabaseException {
|
|
241
|
String query = "";
|
|
242
|
try {
|
|
243
|
verifyParameters(db, table, column);
|
|
244
|
query = "SELECT " + column + " FROM " + table + " WHERE " + column + " = '" + value + "'";
|
|
245
|
final List<String> res = getSimpleListFromSql(db, query);
|
|
246
|
return res != null && res.size() > 0;
|
|
247
|
} catch (final Throwable e) {
|
|
248
|
throw new DatabaseException("Error performing SQL: " + query, e);
|
|
249
|
}
|
|
250
|
}
|
|
251
|
|
|
252
|
public List<Map<?, ?>> describeTable(final String database, final String table) throws DatabaseException {
|
|
253
|
verifyParameters(database, table);
|
|
254
|
|
|
255
|
try {
|
|
256
|
final JdbcTemplate jdbcTemplate = getJdbcTemplate(database);
|
|
257
|
final List<Map<?, ?>> response = new ArrayList<>();
|
|
258
|
final String query = "SELECT * FROM information_schema.columns WHERE table_name = ?";
|
|
259
|
|
|
260
|
for (final Object o : jdbcTemplate.queryForList(query, new Object[] {
|
|
261
|
table
|
|
262
|
})) {
|
|
263
|
if (o instanceof Map<?, ?>) {
|
|
264
|
response.add((Map<?, ?>) o);
|
|
265
|
}
|
|
266
|
}
|
|
267
|
return response;
|
|
268
|
} catch (final DataAccessException e) {
|
|
269
|
throw new DatabaseException(e);
|
|
270
|
}
|
|
271
|
}
|
|
272
|
|
|
273
|
public String dumpTableAsXML(final String db, final String t) throws DatabaseException {
|
|
274
|
return dumpTableAsDoc(db, t).asXML();
|
|
275
|
}
|
|
276
|
|
|
277
|
public Document dumpTableAsDoc(final String db, final String t) throws DatabaseException {
|
|
278
|
final Document doc = DocumentHelper.createDocument();
|
|
279
|
|
|
280
|
final Element root = doc.addElement("DB_TABLE");
|
|
281
|
final Element head = root.addElement("HEADER");
|
|
282
|
|
|
283
|
head.addElement("DATABASE").addAttribute("value", db);
|
|
284
|
head.addElement("TABLE").addAttribute("value", t);
|
|
285
|
head.addElement("DATE").addAttribute("value", DateUtils.now_ISO8601());
|
|
286
|
|
|
287
|
final Element body = root.addElement("BODY");
|
|
288
|
for (final Document d : dumpTableAsList(db, t)) {
|
|
289
|
body.add(d.getRootElement());
|
|
290
|
}
|
|
291
|
return doc;
|
|
292
|
}
|
|
293
|
|
|
294
|
public List<Document> dumpTableAsList(final String db, final String t) throws DatabaseException {
|
|
295
|
final JdbcTemplate jdbcTemplate = getJdbcTemplate(db);
|
|
296
|
|
|
297
|
final List<Document> list = new ArrayList<>();
|
|
298
|
for (final Object o : jdbcTemplate.queryForList("SELECT * FROM " + t)) {
|
|
299
|
if (o instanceof Map<?, ?>) {
|
|
300
|
list.add(rowToDocument((Map<?, ?>) o));
|
|
301
|
}
|
|
302
|
}
|
|
303
|
return list;
|
|
304
|
}
|
|
305
|
|
|
306
|
public Document rowToDocument(final Map<?, ?> map) throws DatabaseException {
|
|
307
|
final Document doc = DocumentHelper.createDocument();
|
|
308
|
final Element row = doc.addElement("ROW");
|
|
309
|
|
|
310
|
for (final Map.Entry<?, ?> entry : map.entrySet()) {
|
|
311
|
final Element col = row.addElement("FIELD");
|
|
312
|
col.addAttribute("name", "" + entry.getKey());
|
|
313
|
addValue(col, entry.getValue());
|
|
314
|
}
|
|
315
|
return doc;
|
|
316
|
}
|
|
317
|
|
|
318
|
public Document getRowByResourceId(final String database, final String table, final String resourceId) throws DatabaseException {
|
|
319
|
verifyParameters(database, table);
|
|
320
|
|
|
321
|
final JdbcTemplate jdbcTemplate = getJdbcTemplate(database);
|
|
322
|
final String query = "SELECT * FROM " + table + " WHERE " + DNET_RESOURCE_ID_FIELD + "=?";
|
|
323
|
|
|
324
|
final Map<?, ?> map = jdbcTemplate.queryForMap(query, resourceId);
|
|
325
|
final Document doc = DocumentHelper.createDocument();
|
|
326
|
|
|
327
|
final Element root = doc.addElement("DB_RECORD");
|
|
328
|
final Element head = root.addElement("HEADER");
|
|
329
|
head.addElement("RESOURCE_IDENTIFIER").addAttribute("value", resourceId);
|
|
330
|
head.addElement("DATABASE").addAttribute("value", database);
|
|
331
|
head.addElement("TABLE").addAttribute("value", table);
|
|
332
|
head.addElement("DATE").addAttribute("value", DateUtils.now_ISO8601());
|
|
333
|
|
|
334
|
final Element body = root.addElement("BODY");
|
|
335
|
|
|
336
|
final Element row = body.addElement("ROW");
|
|
337
|
|
|
338
|
for (final Map.Entry<?, ?> entry : map.entrySet()) {
|
|
339
|
final Element col = row.addElement("FIELD");
|
|
340
|
col.addAttribute("name", "" + entry.getKey());
|
|
341
|
addValue(col, entry.getValue());
|
|
342
|
}
|
|
343
|
|
|
344
|
return doc;
|
|
345
|
}
|
|
346
|
|
|
347
|
private void addValue(final Element elem, final Object value) throws DatabaseException {
|
|
348
|
if (value instanceof Array) {
|
|
349
|
try {
|
|
350
|
final Array arrayValue = (Array) value;
|
|
351
|
for (final Object o : (Object[]) arrayValue.getArray()) {
|
|
352
|
addValue(elem.addElement("ITEM"), o);
|
|
353
|
}
|
|
354
|
} catch (final Exception e) {
|
|
355
|
log.error(e);
|
|
356
|
throw new DatabaseException("Error procsessing a Array", e);
|
|
357
|
}
|
|
358
|
} else if (value != null) {
|
|
359
|
elem.addText(Sanitizer.sanitize(value.toString()));
|
|
360
|
} else {
|
|
361
|
elem.addAttribute("isNull", "true");
|
|
362
|
}
|
|
363
|
}
|
|
364
|
|
|
365
|
private void verifyParameters(final String... params) throws DatabaseException {
|
|
366
|
final Pattern pattern = Pattern.compile("\\w{1,128}");
|
|
367
|
|
|
368
|
for (final String p : params) {
|
|
369
|
log.debug("TESTING SQL PARAM:" + p);
|
|
370
|
if (p == null) {
|
|
371
|
throw new DatabaseException("Parameter is null");
|
|
372
|
} else if (!pattern.matcher(p).matches()) {
|
|
373
|
throw new DatabaseException("Parameter [" + p + "] contains an invalid character");
|
|
374
|
} else {
|
|
375
|
log.debug("TEST OK");
|
|
376
|
}
|
|
377
|
}
|
|
378
|
}
|
|
379
|
|
|
380
|
public void importFromIterable(final String db, final Iterable<String> iterable) throws DatabaseException {
|
|
381
|
verifyParameters(db);
|
|
382
|
|
|
383
|
final DataSource dataSource = dataSourceFactory.createDataSource(db);
|
|
384
|
final JdbcTemplate jdbcTemplate = getJdbcTemplate(db);
|
|
385
|
final TransactionTemplate transactionTemplate = transactionTemplateFactory.createTransactionTemplate(dataSource);
|
|
386
|
|
|
387
|
int counterTotal = 0;
|
|
388
|
|
|
389
|
final long start = DateUtils.now();
|
|
390
|
|
|
391
|
final List<GenericRow> rows = new ArrayList<>();
|
|
392
|
for (final String prof : iterable) {
|
|
393
|
rows.addAll(obtainListOfRows(prof));
|
|
394
|
if (rows.size() > numbersOfRecordsForTransaction) {
|
|
395
|
counterTotal += rows.size();
|
|
396
|
importTransaction(jdbcTemplate, transactionTemplate, rows);
|
|
397
|
rows.clear();
|
|
398
|
}
|
|
399
|
}
|
|
400
|
counterTotal += rows.size();
|
|
401
|
importTransaction(jdbcTemplate, transactionTemplate, rows);
|
|
402
|
|
|
403
|
final long end = DateUtils.now();
|
|
404
|
|
|
405
|
log.info("**********************************************************");
|
|
406
|
log.info("Processed " + counterTotal + " rows in " + (end - start) / 1000 + " seconds");
|
|
407
|
log.info("**********************************************************");
|
|
408
|
}
|
|
409
|
|
|
410
|
private void importTransaction(final JdbcTemplate jdbcTemplate, final TransactionTemplate transactionTemplate, final List<GenericRow> rows)
|
|
411
|
throws DatabaseException {
|
|
412
|
if (rows != null && rows.size() > 0) {
|
|
413
|
importTransactionInternal(jdbcTemplate, transactionTemplate, rows);
|
|
414
|
}
|
|
415
|
}
|
|
416
|
|
|
417
|
private List<GenericRow> importTransactionInternal(final JdbcTemplate jdbcTemplate,
|
|
418
|
final TransactionTemplate transactionTemplate,
|
|
419
|
final List<GenericRow> rows) throws DatabaseException {
|
|
420
|
|
|
421
|
final AtomicReference<DatabaseException> error = new AtomicReference<>();
|
|
422
|
|
|
423
|
try {
|
|
424
|
return transactionTemplate.execute(status -> {
|
|
425
|
final List<GenericRow> ok = Lists.newArrayList();
|
|
426
|
try {
|
|
427
|
for (final GenericRow row : rows) {
|
|
428
|
if (row.isToDelete()) {
|
|
429
|
deleteRow(jdbcTemplate, row.getTable(), row.getFields());
|
|
430
|
} else {
|
|
431
|
addOrUpdateRow(jdbcTemplate, row.getTable(), row.getFields());
|
|
432
|
}
|
|
433
|
ok.add(row);
|
|
434
|
}
|
|
435
|
} catch (final DatabaseException e) {
|
|
436
|
log.warn("Transaction failed", e);
|
|
437
|
status.setRollbackOnly();
|
|
438
|
error.set(e);
|
|
439
|
}
|
|
440
|
return ok;
|
|
441
|
});
|
|
442
|
} finally {
|
|
443
|
if (error.get() != null) { throw error.get(); }
|
|
444
|
}
|
|
445
|
}
|
|
446
|
|
|
447
|
protected void addOrUpdateRow(final JdbcTemplate jdbcTemplate, final String table, final Map<String, Object> rowFields) throws DatabaseException {
|
|
448
|
try {
|
|
449
|
|
|
450
|
if (log.isDebugEnabled()) {
|
|
451
|
log.debug("Adding or updating element to table " + table);
|
|
452
|
}
|
|
453
|
verifyParameters(table);
|
|
454
|
verifyParameters(rowFields.keySet().toArray(new String[rowFields.size()]));
|
|
455
|
|
|
456
|
String fields = "";
|
|
457
|
String values = "";
|
|
458
|
final List<Object> list = new ArrayList<>();
|
|
459
|
|
|
460
|
for (final Map.Entry<String, Object> e : rowFields.entrySet()) {
|
|
461
|
if (!fields.isEmpty()) {
|
|
462
|
fields += ",";
|
|
463
|
}
|
|
464
|
fields += e.getKey();
|
|
465
|
if (!values.isEmpty()) {
|
|
466
|
values += ",";
|
|
467
|
}
|
|
468
|
values += "?";
|
|
469
|
list.add(e.getValue());
|
|
470
|
}
|
|
471
|
|
|
472
|
int count = 0;
|
|
473
|
if (rowFields.containsKey(DNET_RESOURCE_ID_FIELD)) {
|
|
474
|
final List<Object> list2 = new ArrayList<>();
|
|
475
|
list2.addAll(list);
|
|
476
|
list2.add(rowFields.get(DNET_RESOURCE_ID_FIELD));
|
|
477
|
count =
|
|
478
|
jdbcTemplate.update("UPDATE " + table + " SET (" + fields + ") = (" + values + ") WHERE " + DNET_RESOURCE_ID_FIELD + "=?", list2.toArray());
|
|
479
|
}
|
|
480
|
if (count == 0) {
|
|
481
|
jdbcTemplate.update("INSERT INTO " + table + " (" + fields + ") VALUES (" + values + ")", list.toArray());
|
|
482
|
}
|
|
483
|
} catch (final Exception e) {
|
|
484
|
throw new DatabaseException("Error adding or updating record", e);
|
|
485
|
}
|
|
486
|
}
|
|
487
|
|
|
488
|
protected void deleteRow(final JdbcTemplate jdbcTemplate, final String table, final Map<String, Object> rowFields) throws DatabaseException {
|
|
489
|
if (log.isDebugEnabled()) {
|
|
490
|
log.debug("Deleting element from table " + table);
|
|
491
|
}
|
|
492
|
verifyParameters(table);
|
|
493
|
verifyParameters(rowFields.keySet().toArray(new String[rowFields.size()]));
|
|
494
|
|
|
495
|
final List<Object> list = new ArrayList<>();
|
|
496
|
|
|
497
|
String where = "";
|
|
498
|
|
|
499
|
for (final Map.Entry<String, Object> e : rowFields.entrySet()) {
|
|
500
|
if (!where.isEmpty()) {
|
|
501
|
where += " AND ";
|
|
502
|
}
|
|
503
|
where += e.getKey() + "=?";
|
|
504
|
list.add(e.getValue());
|
|
505
|
}
|
|
506
|
|
|
507
|
if (where.isEmpty()) { throw new DatabaseException("Delete condition is empty"); }
|
|
508
|
final int n = jdbcTemplate.update("DELETE FROM " + table + " WHERE " + where, list.toArray());
|
|
509
|
|
|
510
|
if (log.isDebugEnabled()) {
|
|
511
|
log.debug("Number of Deleted records: " + n);
|
|
512
|
}
|
|
513
|
}
|
|
514
|
|
|
515
|
public void deleteRowByResourceId(final String database, final String table, final String resourceIdentifier) throws DatabaseException {
|
|
516
|
verifyParameters(database, table, resourceIdentifier);
|
|
517
|
final JdbcTemplate jdbcTemplate = getJdbcTemplate(database);
|
|
518
|
jdbcTemplate.update("DELETE FROM " + table + " WHERE " + DNET_RESOURCE_ID_FIELD + "=?", resourceIdentifier);
|
|
519
|
}
|
|
520
|
|
|
521
|
public void clearTable(final String database, final String table) throws DatabaseException {
|
|
522
|
verifyParameters(database, table);
|
|
523
|
|
|
524
|
final JdbcTemplate jdbcTemplate = getJdbcTemplate(database);
|
|
525
|
jdbcTemplate.update("DELETE FROM " + table);
|
|
526
|
}
|
|
527
|
|
|
528
|
public void prepareManagementOfTable(final String database, final String table) throws DatabaseException {
|
|
529
|
verifyParameters(database, table);
|
|
530
|
final JdbcTemplate jdbcTemplate = getJdbcTemplate(database);
|
|
531
|
|
|
532
|
if (!isManagedTable(jdbcTemplate, table)) {
|
|
533
|
jdbcTemplate.update(getSQLFromTemplate("manageTable", database, table, null));
|
|
534
|
log.info("Added management of table " + table);
|
|
535
|
}
|
|
536
|
}
|
|
537
|
|
|
538
|
public void removeManagementOfTable(final String database, final String table) throws DatabaseException {
|
|
539
|
verifyParameters(database, table);
|
|
540
|
final JdbcTemplate jdbcTemplate = getJdbcTemplate(database);
|
|
541
|
|
|
542
|
if (isManagedTable(jdbcTemplate, table)) {
|
|
543
|
jdbcTemplate.update(getSQLFromTemplate("unmanageTable", database, table, null));
|
|
544
|
log.info("Removed management of table " + table);
|
|
545
|
}
|
|
546
|
}
|
|
547
|
|
|
548
|
public boolean isManagedTable(final String database, final String table) throws DatabaseException {
|
|
549
|
verifyParameters(database, table);
|
|
550
|
final JdbcTemplate jdbcTemplate = getJdbcTemplate(database);
|
|
551
|
return isManagedTable(jdbcTemplate, table);
|
|
552
|
}
|
|
553
|
|
|
554
|
private boolean isManagedTable(final JdbcTemplate jdbcTemplate, final String table) {
|
|
555
|
return jdbcTemplate
|
|
556
|
.queryForObject("SELECT count(*) FROM information_schema.columns WHERE table_name = ? AND column_name = ?", Integer.class, table, DNET_RESOURCE_ID_FIELD) == 1;
|
|
557
|
}
|
|
558
|
|
|
559
|
public boolean isLoggedTable(final String database, final String table) throws DatabaseException {
|
|
560
|
verifyParameters(database, table);
|
|
561
|
final JdbcTemplate jdbcTemplate = getJdbcTemplate(database);
|
|
562
|
return isLoggedTable(jdbcTemplate, table);
|
|
563
|
}
|
|
564
|
|
|
565
|
private boolean isLoggedTable(final JdbcTemplate jdbcTemplate, final String table) {
|
|
566
|
return jdbcTemplate.queryForObject("SELECT count(*) FROM information_schema.tables WHERE table_name = ?", Integer.class, table + "_log") == 1;
|
|
567
|
}
|
|
568
|
|
|
569
|
public String getDefaultDnetIdentifier(final String database, final String table) throws DatabaseException {
|
|
570
|
verifyParameters(database, table);
|
|
571
|
final JdbcTemplate jdbcTemplate = getJdbcTemplate(database);
|
|
572
|
if (isManagedTable(jdbcTemplate, table)) {
|
|
573
|
return jdbcTemplate.queryForObject("SELECT column_default FROM information_schema.columns WHERE table_name = ? AND column_name = ?", new Object[] {
|
|
574
|
table, DNET_RESOURCE_ID_FIELD
|
|
575
|
}, String.class);
|
|
576
|
}
|
|
577
|
return "";
|
|
578
|
}
|
|
579
|
|
|
580
|
public void reassignDefaultDnetIdentifiers(final String db) throws DatabaseException {
|
|
581
|
for (final String t : listCommonDBTables(db)) {
|
|
582
|
reassignDefaultDnetIdentifiers(db, t);
|
|
583
|
}
|
|
584
|
}
|
|
585
|
|
|
586
|
public void reassignDefaultDnetIdentifiers(final String db, final String t) throws DatabaseException {
|
|
587
|
if (!isManagedTable(db, t)) { return; }
|
|
588
|
|
|
589
|
final SqlRowSet rows =
|
|
590
|
executeSql(db, "SELECT pg_attribute.attname as pkey FROM pg_index, pg_class, pg_attribute " + "WHERE pg_class.oid = '" + t + "'::regclass "
|
|
591
|
+ "AND indrelid = pg_class.oid " + "AND pg_attribute.attrelid = pg_class.oid "
|
|
592
|
+ "AND pg_attribute.attnum = any(pg_index.indkey) AND indisprimary " + "ORDER BY pkey", SqlRowSet.class);
|
|
593
|
|
|
594
|
String defaultValue = "";
|
|
595
|
while (rows.next()) {
|
|
596
|
if (!defaultValue.isEmpty()) {
|
|
597
|
defaultValue += "||'@@'||";
|
|
598
|
}
|
|
599
|
defaultValue += rows.getString("pkey");
|
|
600
|
}
|
|
601
|
executeSql(db, "UPDATE " + t + " SET " + DatabaseUtils.DNET_RESOURCE_ID_FIELD + " = " + defaultValue);
|
|
602
|
log.info("Reassigned dnetId for table " + t);
|
|
603
|
}
|
|
604
|
|
|
605
|
public String getSQLFromTemplate(final String sqlTemplate, final String db, final String table, Map<String, Object> map) {
|
|
606
|
if (map == null) {
|
|
607
|
map = new HashMap<>();
|
|
608
|
}
|
|
609
|
|
|
610
|
map.put("mainDB", defaultDB);
|
|
611
|
map.put("db", db);
|
|
612
|
map.put("table", table);
|
|
613
|
map.put("idField", DNET_RESOURCE_ID_FIELD);
|
|
614
|
|
|
615
|
return VelocityEngineUtils.mergeTemplateIntoString(velocityEngine, "eu/dnetlib/enabling/database/velocity/" + sqlTemplate + ".sql.vm", "UTF-8", map);
|
|
616
|
}
|
|
617
|
|
|
618
|
public List<GenericRow> obtainListOfRows(final String xml) throws DatabaseException {
|
|
619
|
try {
|
|
620
|
final Document doc = new SAXReader().read(new StringReader(xml));
|
|
621
|
|
|
622
|
final List<GenericRow> list = new ArrayList<>();
|
|
623
|
|
|
624
|
for (final Object or : doc.selectNodes("//ROW")) {
|
|
625
|
final Element row = (Element) or;
|
|
626
|
|
|
627
|
final String table = row.valueOf("@table");
|
|
628
|
|
|
629
|
if (table == null || table.isEmpty()) { throw new DatabaseException("Attribute table is missing in XSLT"); }
|
|
630
|
|
|
631
|
final boolean toDelete = "deleted".equals(row.valueOf("@status"));
|
|
632
|
|
|
633
|
final Map<String, Object> fields = new HashMap<>();
|
|
634
|
|
|
635
|
for (final Object of : row.selectNodes("./FIELD")) {
|
|
636
|
final Node node = (Node) of;
|
|
637
|
final String key = node.valueOf("@name");
|
|
638
|
final String type = node.valueOf("@type");
|
|
639
|
final String format = node.valueOf("@format");
|
|
640
|
final String valueS = node.getText().trim();
|
|
641
|
|
|
642
|
if (key != null && !key.isEmpty()) {
|
|
643
|
|
|
644
|
final Object value;
|
|
645
|
if (type == null || type.isEmpty()) {
|
|
646
|
// String types (text, char(), varchar())
|
|
647
|
value = valueS;
|
|
648
|
} else {
|
|
649
|
try {
|
|
650
|
// type array should be evaluated before the null object
|
|
651
|
if (type.equals("array")) {
|
|
652
|
final List<String> arrayItems = new ArrayList<>();
|
|
653
|
for (final Object oi : node.selectNodes("./ITEM")) {
|
|
654
|
arrayItems.add(((Node) oi).getText());
|
|
655
|
}
|
|
656
|
value = new SqlTextArrayValue(arrayItems.toArray(new String[arrayItems.size()]));
|
|
657
|
} else if ("".equals(valueS)) {
|
|
658
|
// an empty string in a typed field means null
|
|
659
|
value = null;
|
|
660
|
} else if (type.equals("int")) {
|
|
661
|
value = Integer.parseInt(valueS);
|
|
662
|
} else if (type.equals("float")) {
|
|
663
|
value = Float.parseFloat(valueS);
|
|
664
|
} else if (type.equals("boolean")) {
|
|
665
|
value = parseBoolean(valueS);
|
|
666
|
} else if (type.equals("date")) {
|
|
667
|
value = parseDate(valueS, format);
|
|
668
|
} else if (type.equals("iso8601Date")) {
|
|
669
|
final DateTime date = ISODateTimeFormat.dateTimeParser().parseDateTime(valueS);
|
|
670
|
value = date.toDate();
|
|
671
|
// value = new DateUtils().parse(valueS);
|
|
672
|
} else {
|
|
673
|
log.error("Invalid type: " + type);
|
|
674
|
throw new DatabaseException("Invalid type: " + type);
|
|
675
|
}
|
|
676
|
} catch (final IllegalArgumentException e) {
|
|
677
|
log.fatal("cannot convert '" + valueS + "' to " + type, e);
|
|
678
|
throw e;
|
|
679
|
}
|
|
680
|
}
|
|
681
|
fields.put(key, value);
|
|
682
|
}
|
|
683
|
}
|
|
684
|
|
|
685
|
list.add(new GenericRow(table, fields, toDelete));
|
|
686
|
}
|
|
687
|
return list;
|
|
688
|
} catch (final Exception e) {
|
|
689
|
log.error("Error obtaining list of rows from xml: " + xml);
|
|
690
|
throw new DatabaseException(e);
|
|
691
|
}
|
|
692
|
}
|
|
693
|
|
|
694
|
protected boolean parseBoolean(final String s) {
|
|
695
|
if (TRUE_VALUES.contains(s.toLowerCase().trim())) { return true; }
|
|
696
|
if (FALSE_VALUES.contains(s.toLowerCase().trim())) { return false; }
|
|
697
|
|
|
698
|
return BooleanUtils.toBoolean(s);
|
|
699
|
}
|
|
700
|
|
|
701
|
public void setManaged(final String dbName, final boolean managed) throws DatabaseException {
|
|
702
|
verifyParameters(dbName);
|
|
703
|
final JdbcTemplate jdbcTemplate = getJdbcTemplate(dbName);
|
|
704
|
if (managed) {
|
|
705
|
jdbcTemplate.update("COMMENT ON DATABASE " + dbName + " IS 'isManaged'");
|
|
706
|
} else {
|
|
707
|
jdbcTemplate.update("COMMENT ON DATABASE " + dbName + " IS NULL");
|
|
708
|
}
|
|
709
|
}
|
|
710
|
|
|
711
|
// public for testing
|
|
712
|
public Date parseDate(final String date, String format) {
|
|
713
|
if (format == null || format.isEmpty()) {
|
|
714
|
format = SQL_DATE_FORMAT;
|
|
715
|
}
|
|
716
|
try {
|
|
717
|
final java.util.Date parsed = new SimpleDateFormat(format).parse(date);
|
|
718
|
final String ret = new SimpleDateFormat(SQL_DATE_FORMAT).format(parsed);
|
|
719
|
return Date.valueOf(ret);
|
|
720
|
} catch (final ParseException e) {
|
|
721
|
return null;
|
|
722
|
}
|
|
723
|
}
|
|
724
|
|
|
725
|
private JdbcTemplate getJdbcTemplate(final String dbName) {
|
|
726
|
final JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(dbName);
|
|
727
|
jdbcTemplate.setFetchSize(getFetchSize());
|
|
728
|
return jdbcTemplate;
|
|
729
|
}
|
|
730
|
|
|
731
|
@Required
|
|
732
|
public void setVelocityEngine(final VelocityEngine velocityEngine) {
|
|
733
|
this.velocityEngine = velocityEngine;
|
|
734
|
}
|
|
735
|
|
|
736
|
public String getDbPrefix() {
|
|
737
|
return dbPrefix;
|
|
738
|
}
|
|
739
|
|
|
740
|
@Required
|
|
741
|
public void setDbPrefix(final String dbPrefix) {
|
|
742
|
this.dbPrefix = dbPrefix;
|
|
743
|
}
|
|
744
|
|
|
745
|
public DataSourceFactory getDataSourceFactory() {
|
|
746
|
return dataSourceFactory;
|
|
747
|
}
|
|
748
|
|
|
749
|
@Required
|
|
750
|
public void setDataSourceFactory(final DataSourceFactory dataSourceFactory) {
|
|
751
|
this.dataSourceFactory = dataSourceFactory;
|
|
752
|
}
|
|
753
|
|
|
754
|
public JdbcTemplateFactory getJdbcTemplateFactory() {
|
|
755
|
return jdbcTemplateFactory;
|
|
756
|
}
|
|
757
|
|
|
758
|
@Required
|
|
759
|
public void setJdbcTemplateFactory(final JdbcTemplateFactory jdbcTemplateFactory) {
|
|
760
|
this.jdbcTemplateFactory = jdbcTemplateFactory;
|
|
761
|
}
|
|
762
|
|
|
763
|
public TransactionTemplateFactory getTransactionTemplateFactory() {
|
|
764
|
return transactionTemplateFactory;
|
|
765
|
}
|
|
766
|
|
|
767
|
@Required
|
|
768
|
public void setTransactionTemplateFactory(final TransactionTemplateFactory transactionTemplateFactory) {
|
|
769
|
this.transactionTemplateFactory = transactionTemplateFactory;
|
|
770
|
}
|
|
771
|
|
|
772
|
public int getNumbersOfRecordsForTransaction() {
|
|
773
|
return numbersOfRecordsForTransaction;
|
|
774
|
}
|
|
775
|
|
|
776
|
@Required
|
|
777
|
public void setNumbersOfRecordsForTransaction(final int numbersOfRecordsForTransaction) {
|
|
778
|
this.numbersOfRecordsForTransaction = numbersOfRecordsForTransaction;
|
|
779
|
}
|
|
780
|
|
|
781
|
public String getDefaultDB() {
|
|
782
|
return defaultDB;
|
|
783
|
}
|
|
784
|
|
|
785
|
@Required
|
|
786
|
public void setDefaultDB(final String defaultDB) {
|
|
787
|
this.defaultDB = defaultDB;
|
|
788
|
}
|
|
789
|
|
|
790
|
public int getFetchSize() {
|
|
791
|
return fetchSize;
|
|
792
|
}
|
|
793
|
|
|
794
|
public void setFetchSize(final int fetchSize) {
|
|
795
|
this.fetchSize = fetchSize;
|
|
796
|
}
|
|
797
|
|
|
798
|
public class TableDates {
|
|
799
|
|
|
800
|
private java.sql.Date lastInsert;
|
|
801
|
private java.sql.Date lastUpdate;
|
|
802
|
private java.sql.Date lastDelete;
|
|
803
|
|
|
804
|
public java.sql.Date getLastInsert() {
|
|
805
|
return lastInsert;
|
|
806
|
}
|
|
807
|
|
|
808
|
public void setLastInsert(final Date lastInsert) {
|
|
809
|
this.lastInsert = lastInsert;
|
|
810
|
}
|
|
811
|
|
|
812
|
public Date getLastUpdate() {
|
|
813
|
return lastUpdate;
|
|
814
|
}
|
|
815
|
|
|
816
|
public void setLastUpdate(final Date lastUpdate) {
|
|
817
|
this.lastUpdate = lastUpdate;
|
|
818
|
}
|
|
819
|
|
|
820
|
public Date getLastDelete() {
|
|
821
|
return lastDelete;
|
|
822
|
}
|
|
823
|
|
|
824
|
public void setLastDelete(final Date lastDelete) {
|
|
825
|
this.lastDelete = lastDelete;
|
|
826
|
}
|
|
827
|
}
|
|
828
|
|
|
829
|
}
|