/modules/cnr-enabling-database-service/trunk/src/main/java/eu/dnetlib/enabling/database/utils/DatabaseUtils.java - Annotate - D-Net - D-Net project tracking tool

dnet45/modules/cnr-enabling-database-service/trunk/src/main/java/eu/dnetlib/enabling/database/utils/DatabaseUtils.java @ 62941

-            sandro.lab
+package eu.dnetlib.enabling.database.utils;
 import java.io.StringReader;
-            michele.ar
+import java.sql.Array;
 import java.sql.Connection;
-            sandro.lab
+import java.sql.Date;
-            michele.ar
+import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.ResultSetMetaData;
 import java.sql.SQLException;
-            sandro.lab
+import java.text.ParseException;
 import java.text.SimpleDateFormat;
-            michele.ar
+import java.util.ArrayList;
 import java.util.HashMap;
 import java.util.List;
 import java.util.Map;
 import java.util.Set;
-            claudio.at
+import java.util.concurrent.ArrayBlockingQueue;
-            sandro.lab
+import java.util.concurrent.BlockingQueue;
 import java.util.concurrent.Executors;
 import java.util.concurrent.TimeUnit;
-            claudio.at
+import java.util.concurrent.atomic.AtomicReference;
-            sandro.lab
+import java.util.regex.Pattern;
             michele.ar
-            sandro.lab
+import javax.sql.DataSource;
-            claudio.at
+import org.apache.commons.lang.BooleanUtils;
-            sandro.lab
+import org.apache.commons.logging.Log;
 import org.apache.commons.logging.LogFactory;
 import org.apache.velocity.app.VelocityEngine;
 import org.dom4j.Document;
 import org.dom4j.DocumentHelper;
 import org.dom4j.Element;
 import org.dom4j.Node;
 import org.dom4j.io.SAXReader;
 import org.joda.time.DateTime;
 import org.joda.time.format.ISODateTimeFormat;
 import org.springframework.beans.factory.annotation.Required;
 import org.springframework.dao.DataAccessException;
 import org.springframework.jdbc.core.JdbcTemplate;
 import org.springframework.jdbc.support.rowset.SqlRowSet;
 import org.springframework.transaction.support.TransactionTemplate;
 import org.springframework.ui.velocity.VelocityEngineUtils;
-            michele.ar
+import com.google.common.collect.Lists;
 import com.google.common.collect.Queues;
 import com.google.common.collect.Sets;
 import eu.dnetlib.enabling.database.DataSourceFactory;
 import eu.dnetlib.enabling.database.TransactionTemplateFactory;
 import eu.dnetlib.enabling.database.objects.DnetDatabase;
 import eu.dnetlib.enabling.database.rmi.DatabaseException;
 import eu.dnetlib.miscutils.datetime.DateUtils;
 import eu.dnetlib.miscutils.functional.string.Sanitizer;
-            sandro.lab
+public class DatabaseUtils {
-            alessia.ba
+	public static final String DNET_RESOURCE_ID_FIELD = "_dnet_resource_identifier_";
 	public static final int BLOCKING_QUEUE_TIMEOUT = 300;
-            sandro.lab
+	private static final String SQL_DATE_FORMAT = "yyyy-MM-dd";
-            alessia.ba
+	private static final Log log = LogFactory.getLog(DatabaseUtils.class); // NOPMD by marko on 11/24/08 5:02 PM
 	private static final int BLOCKING_QUEUE_SIZE = 200;
 	private static final Set<String> TRUE_VALUES = Sets.newHashSet("true", "t", "yes", "y", "vero", "v");
 	private static final Set<String> FALSE_VALUES = Sets.newHashSet("false", "f", "no", "n", "falso");
-            sandro.lab
+	private DataSourceFactory dataSourceFactory;
 	private JdbcTemplateFactory jdbcTemplateFactory;
 	private TransactionTemplateFactory transactionTemplateFactory;
 	private String defaultDB;
 	private VelocityEngine velocityEngine;
 	private String dbPrefix;
 	private int numbersOfRecordsForTransaction;
-            claudio.at
+	private int fetchSize = 100;
             sandro.lab
 	public List<String> listCommonDBTables(final String database) throws DatabaseException {
-            michele.ar
+		final String query =
 			"SELECT table_name FROM information_schema.tables " + "WHERE table_schema = 'public' " + "AND table_type != 'VIEW' "
 				+ "AND table_name NOT LIKE '%_log'";
-            sandro.lab
+		return getTypedListFromSql(database, query, String.class);
+	}
 	public List<String> listCommonDBViews(final String database) throws DatabaseException {
-            michele.ar
+		final String query =
 			"SELECT table_name FROM information_schema.tables " + "WHERE table_schema = 'public' " + "AND table_type = 'VIEW' "
 				+ "AND table_name NOT LIKE '%_log'";
-            sandro.lab
+		return getTypedListFromSql(database, query, String.class);
+	}
-            claudio.at
+	public Map<String, TableDates> getTableDatesForDB(final String db) throws DatabaseException {
-            michele.ar
+		final Map<String, TableDates> res = new HashMap<>();
             claudio.at
-            michele.ar
+		for (final String table : listCommonDBTables(db)) {
-            claudio.at
+			try {
-            michele.ar
+				final TableDates dates = new TableDates();
             claudio.at
-            michele.ar
+				final String query =
 					"select lastinsert, lastupdate, lastdelete from " + "(select max(date) as lastinsert from " + table
 						+ "_log where operation='insert') as t1, " + "(select max(date) as lastupdate from " + table
 						+ "_log where operation='update') as t2, " + "(select max(date) as lastdelete from " + table
 						+ "_log where operation='delete') as t3";
             claudio.at
-            michele.ar
+				final SqlRowSet srs = executeSql(db, query, SqlRowSet.class);
-            claudio.at
+				if (srs.next()) {
 					dates.setLastInsert(srs.getDate("lastinsert"));
 					dates.setLastUpdate(srs.getDate("lastupdate"));
 					dates.setLastDelete(srs.getDate("lastdelete"));
+				}
 				res.put(table, dates);
-            michele.ar
+			} catch (final Exception e) {
-            claudio.at
+				log.warn("Error obtaing dates for table " + table, e);
+			}
+		}
 		return res;
+	}
-            sandro.lab
+	public List<DnetDatabase> listAllDatabases() throws DatabaseException {
-            claudio.at
+		final String query =
-            michele.ar
+			"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 '"
 				+ dbPrefix + "%' ORDER BY d.datname DESC";
-            claudio.at
+		final JdbcTemplate jdbcTemplate = getJdbcTemplate(defaultDB);
             claudio.at
-            sandro.lab
+		final List<DnetDatabase> list = Lists.newArrayList();
-            michele.ar
+		for (final Map<String, Object> map : jdbcTemplate.queryForList(query)) {
-            sandro.lab
+			list.add(new DnetDatabase(map.get("db").toString(), Boolean.parseBoolean(map.get("managed").toString())));
+		}
 		return list;
             claudio.at
             sandro.lab
 	public <T> List<T> getTypedListFromSql(final String dbName, final String query, final Class<T> clazz) throws DatabaseException {
 		try {
-            michele.ar
+			final List<T> list = new ArrayList<>();
 			for (final Object obj : getJdbcTemplate(dbName).queryForList(query, clazz)) {
-            sandro.lab
+				list.add(clazz.cast(obj));
+			}
 			return list;
-            michele.ar
+		} catch (final DataAccessException e) {
-            sandro.lab
+			throw new DatabaseException(e);
+		}
+	}
 	public List<String> getSimpleListFromSql(final String dbName, final String query) throws DatabaseException {
             claudio.at
-            michele.ar
+		final JdbcTemplate jdbcTemplate = getJdbcTemplate(dbName);
             sandro.lab
 		try {
-            michele.ar
+			final List<String> list = new ArrayList<>();
 			for (final Object obj : jdbcTemplate.queryForList(query)) {
-            sandro.lab
+				list.add(obj.toString());
+			}
 			return list;
-            michele.ar
+		} catch (final DataAccessException e) {
-            sandro.lab
+			throw new DatabaseException(e);
+		}
+	}
 	public void executeSql(final String db, final String query) throws DatabaseException {
 		executeSql(db, query, Void.class);
+	}
 	@SuppressWarnings("unchecked")
 	public <T> T executeSql(final String dbName, final String query, final Class<T> clazz) throws DatabaseException {
             claudio.at
-            claudio.at
+		if (clazz == BlockingQueue.class) {
 			log.debug("Creating Queue");
             sandro.lab
-            claudio.at
+			final ArrayBlockingQueue<Document> queue = Queues.newArrayBlockingQueue(BLOCKING_QUEUE_SIZE);
-            claudio.at
+			Executors.newSingleThreadExecutor().submit(() -> {
-            claudio.at
+				final DataSource ds = dataSourceFactory.createDataSource(dbName);
 				try (final Connection con = getConnection(ds);
-            michele.ar
+					final PreparedStatement stm = getStm(query, con);
 					final ResultSet rs = stm.executeQuery()) {
             claudio.at
-            claudio.at
+					rs.setFetchSize(getFetchSize());
-            claudio.at
+					boolean timeout = false;
-            claudio.at
+					log.info(String.format("[Thread Id %s] starting to populate queue", Thread.currentThread().getId()));
-            claudio.at
+					while (rs.next()) {
-            michele.ar
+						final ResultSetMetaData md = rs.getMetaData();
 						final Map<String, Object> row = new HashMap<>();
-            claudio.at
+						for (int i = 1; i <= md.getColumnCount(); i++) {
 							row.put(md.getColumnName(i), rs.getObject(i));
             claudio.at
-            claudio.at
+						if (!enqueue(queue, row)) {
-            claudio.at
+							timeout = true;
-            claudio.at
+							break;
+						}
             claudio.at
-            claudio.at
+					if (timeout) {
-            michele.ar
+						log.warn(String.format("[Thread Id %s] queue full, consumer did not consume for %s seconds, I give up", Thread.currentThread()
 							.getId(), BLOCKING_QUEUE_TIMEOUT));
-            claudio.at
+						return;
             claudio.at
-            claudio.at
+					// An empty Map indicates the end of the resultset
 					enqueue(queue, new HashMap<>());
-            claudio.at
+				} catch (SQLException | DatabaseException e) {
-            claudio.at
+					throw new RuntimeException(e);
+				}
 			});
             sandro.lab
-            claudio.at
+			log.debug("Returned Queue");
             sandro.lab
-            claudio.at
+			return (T) queue;
+		}
-            claudio.at
+		final JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(dbName);
-            michele.ar
+		if (clazz == Integer.class) {
 			return (T) jdbcTemplate.queryForObject(query, Integer.class);
 		} else if (clazz == List.class) {
 			return (T) jdbcTemplate.queryForList(query);
 		} else if (clazz == Map.class) {
 			return (T) jdbcTemplate.queryForMap(query);
 		} else if (clazz == SqlRowSet.class) {
 			return (T) jdbcTemplate.queryForRowSet(query);
 		} else {
-            claudio.at
+			jdbcTemplate.update(query);
 			return null;
             sandro.lab
+	}
-            claudio.at
+	private boolean enqueue(final ArrayBlockingQueue<Document> q, final Map<String, Object> row) throws DatabaseException {
-            claudio.at
+		try {
-            claudio.at
+			return q.offer(rowToDocument(row), BLOCKING_QUEUE_TIMEOUT, TimeUnit.SECONDS);
-            michele.ar
+		} catch (final InterruptedException e) {
-            claudio.at
+			log.error("Error putting element in queue");
 			throw new RuntimeException(e);
+		}
+	}
 	private PreparedStatement getStm(final String query, final Connection con) throws SQLException {
 		final PreparedStatement stm = con.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY);
 		stm.setFetchSize(getFetchSize());
 		return stm;
+	}
-            claudio.at
+	private Connection getConnection(final DataSource dataSource) throws SQLException {
 		final Connection conn = dataSource.getConnection();
 		conn.setAutoCommit(false);
 		return conn;
+	}
-            alessia.ba
+	public boolean contains(final String db, final String table, final String column, final String value) throws DatabaseException {
-            sandro.lab
+		String query = "";
 		try {
 			verifyParameters(db, table, column);
 			query = "SELECT " + column + " FROM " + table + " WHERE " + column + " = '" + value + "'";
-            michele.ar
+			final List<String> res = getSimpleListFromSql(db, query);
-            claudio.at
+			return res != null && res.size() > 0;
-            michele.ar
+		} catch (final Throwable e) {
-            alessia.ba
+			throw new DatabaseException("Error performing SQL: " + query, e);
             sandro.lab
+	}
 	public List<Map<?, ?>> describeTable(final String database, final String table) throws DatabaseException {
 		verifyParameters(database, table);
 		try {
-            claudio.at
+			final JdbcTemplate jdbcTemplate = getJdbcTemplate(database);
-            michele.ar
+			final List<Map<?, ?>> response = new ArrayList<>();
 			final String query = "SELECT * FROM information_schema.columns WHERE table_name = ?";
             sandro.lab
-            michele.ar
+			for (final Object o : jdbcTemplate.queryForList(query, new Object[] {
 				table
 			})) {
-            sandro.lab
+				if (o instanceof Map<?, ?>) {
 					response.add((Map<?, ?>) o);
+				}
+			}
 			return response;
-            michele.ar
+		} catch (final DataAccessException e) {
-            sandro.lab
+			throw new DatabaseException(e);
+		}
+	}
 	public String dumpTableAsXML(final String db, final String t) throws DatabaseException {
 		return dumpTableAsDoc(db, t).asXML();
+	}
 	public Document dumpTableAsDoc(final String db, final String t) throws DatabaseException {
-            michele.ar
+		final Document doc = DocumentHelper.createDocument();
             sandro.lab
-            michele.ar
+		final Element root = doc.addElement("DB_TABLE");
 		final Element head = root.addElement("HEADER");
             sandro.lab
 		head.addElement("DATABASE").addAttribute("value", db);
 		head.addElement("TABLE").addAttribute("value", t);
 		head.addElement("DATE").addAttribute("value", DateUtils.now_ISO8601());
-            michele.ar
+		final Element body = root.addElement("BODY");
 		for (final Document d : dumpTableAsList(db, t)) {
-            sandro.lab
+			body.add(d.getRootElement());
+		}
 		return doc;
+	}
 	public List<Document> dumpTableAsList(final String db, final String t) throws DatabaseException {
-            claudio.at
+		final JdbcTemplate jdbcTemplate = getJdbcTemplate(db);
             sandro.lab
-            michele.ar
+		final List<Document> list = new ArrayList<>();
 		for (final Object o : jdbcTemplate.queryForList("SELECT * FROM " + t)) {
-            sandro.lab
+			if (o instanceof Map<?, ?>) {
 				list.add(rowToDocument((Map<?, ?>) o));
+			}
+		}
 		return list;
+	}
 	public Document rowToDocument(final Map<?, ?> map) throws DatabaseException {
-            claudio.at
+		final Document doc = DocumentHelper.createDocument();
 		final Element row = doc.addElement("ROW");
             sandro.lab
-            michele.ar
+		for (final Map.Entry<?, ?> entry : map.entrySet()) {
 			final Element col = row.addElement("FIELD");
-            sandro.lab
+			col.addAttribute("name", "" + entry.getKey());
 			addValue(col, entry.getValue());
+		}
 		return doc;
+	}
 	public Document getRowByResourceId(final String database, final String table, final String resourceId) throws DatabaseException {
 		verifyParameters(database, table);
-            michele.ar
+		final JdbcTemplate jdbcTemplate = getJdbcTemplate(database);
 		final String query = "SELECT * FROM " + table + " WHERE " + DNET_RESOURCE_ID_FIELD + "=?";
             sandro.lab
-            michele.ar
+		final Map<?, ?> map = jdbcTemplate.queryForMap(query, resourceId);
 		final Document doc = DocumentHelper.createDocument();
             sandro.lab
-            michele.ar
+		final Element root = doc.addElement("DB_RECORD");
 		final Element head = root.addElement("HEADER");
-            sandro.lab
+		head.addElement("RESOURCE_IDENTIFIER").addAttribute("value", resourceId);
 		head.addElement("DATABASE").addAttribute("value", database);
 		head.addElement("TABLE").addAttribute("value", table);
 		head.addElement("DATE").addAttribute("value", DateUtils.now_ISO8601());
-            michele.ar
+		final Element body = root.addElement("BODY");
             sandro.lab
-            michele.ar
+		final Element row = body.addElement("ROW");
             sandro.lab
-            michele.ar
+		for (final Map.Entry<?, ?> entry : map.entrySet()) {
 			final Element col = row.addElement("FIELD");
-            sandro.lab
+			col.addAttribute("name", "" + entry.getKey());
 			addValue(col, entry.getValue());
+		}
 		return doc;
+	}
 	private void addValue(final Element elem, final Object value) throws DatabaseException {
 		if (value instanceof Array) {
 			try {
-            claudio.at
+				final Array arrayValue = (Array) value;
-            michele.ar
+				for (final Object o : (Object[]) arrayValue.getArray()) {
-            sandro.lab
+					addValue(elem.addElement("ITEM"), o);
+				}
-            michele.ar
+			} catch (final Exception e) {
-            claudio.at
+				log.error(e);
-            sandro.lab
+				throw new DatabaseException("Error procsessing a Array", e);
+			}
 		} else if (value != null) {
 			elem.addText(Sanitizer.sanitize(value.toString()));
 		} else {
 			elem.addAttribute("isNull", "true");
+		}
+	}
 	private void verifyParameters(final String... params) throws DatabaseException {
-            michele.ar
+		final Pattern pattern = Pattern.compile("\\w{1,128}");
             sandro.lab
-            michele.ar
+		for (final String p : params) {
-            sandro.lab
+			log.debug("TESTING SQL PARAM:" + p);
-            michele.ar
+			if (p == null) {
 				throw new DatabaseException("Parameter is null");
 			} else if (!pattern.matcher(p).matches()) {
 				throw new DatabaseException("Parameter [" + p + "] contains an invalid character");
 			} else {
-            sandro.lab
+				log.debug("TEST OK");
+			}
+		}
+	}
 	public void importFromIterable(final String db, final Iterable<String> iterable) throws DatabaseException {
 		verifyParameters(db);
 		final DataSource dataSource = dataSourceFactory.createDataSource(db);
-            claudio.at
+		final JdbcTemplate jdbcTemplate = getJdbcTemplate(db);
-            sandro.lab
+		final TransactionTemplate transactionTemplate = transactionTemplateFactory.createTransactionTemplate(dataSource);
 		int counterTotal = 0;
-            michele.ar
+		final long start = DateUtils.now();
             sandro.lab
-            michele.ar
+		final List<GenericRow> rows = new ArrayList<>();
 		for (final String prof : iterable) {
-            claudio.at
+			rows.addAll(obtainListOfRows(prof));
-            sandro.lab
+			if (rows.size() > numbersOfRecordsForTransaction) {
 				counterTotal += rows.size();
-            claudio.at
+				importTransaction(jdbcTemplate, transactionTemplate, rows);
-            sandro.lab
+				rows.clear();
+			}
+		}
 		counterTotal += rows.size();
-            claudio.at
+		importTransaction(jdbcTemplate, transactionTemplate, rows);
             sandro.lab
-            michele.ar
+		final long end = DateUtils.now();
             sandro.lab
 		log.info("**********************************************************");
-            michele.ar
+		log.info("Processed " + counterTotal + " rows in " + (end - start) / 1000 + " seconds");
-            sandro.lab
+		log.info("**********************************************************");
+	}
-            michele.ar
+	private void importTransaction(final JdbcTemplate jdbcTemplate, final TransactionTemplate transactionTemplate, final List<GenericRow> rows)
 		throws DatabaseException {
-            claudio.at
+		if (rows != null && rows.size() > 0) {
 			importTransactionInternal(jdbcTemplate, transactionTemplate, rows);
             sandro.lab
+	}
 	private List<GenericRow> importTransactionInternal(final JdbcTemplate jdbcTemplate,
-            michele.ar
+		final TransactionTemplate transactionTemplate,
 		final List<GenericRow> rows) throws DatabaseException {
             sandro.lab
-            michele.ar
+		final AtomicReference<DatabaseException> error = new AtomicReference<>();
             claudio.at
 		try {
-            claudio.at
+			return transactionTemplate.execute(status -> {
 				final List<GenericRow> ok = Lists.newArrayList();
 				try {
-            michele.ar
+					for (final GenericRow row : rows) {
-            claudio.at
+						if (row.isToDelete()) {
 							deleteRow(jdbcTemplate, row.getTable(), row.getFields());
 						} else {
 							addOrUpdateRow(jdbcTemplate, row.getTable(), row.getFields());
             sandro.lab
-            claudio.at
+						ok.add(row);
             sandro.lab
-            michele.ar
+				} catch (final DatabaseException e) {
-            claudio.at
+					log.warn("Transaction failed", e);
 					status.setRollbackOnly();
 					error.set(e);
             sandro.lab
-            claudio.at
+				return ok;
-            claudio.at
+			});
 		} finally {
-            michele.ar
+			if (error.get() != null) { throw error.get(); }
             claudio.at
             sandro.lab
-            claudio.at
+	protected void addOrUpdateRow(final JdbcTemplate jdbcTemplate, final String table, final Map<String, Object> rowFields) throws DatabaseException {
-            sandro.lab
+		try {
 			if (log.isDebugEnabled()) {
-            claudio.at
+				log.debug("Adding or updating element to table " + table);
             sandro.lab
-            claudio.at
+			verifyParameters(table);
 			verifyParameters(rowFields.keySet().toArray(new String[rowFields.size()]));
             sandro.lab
 			String fields = "";
 			String values = "";
-            michele.ar
+			final List<Object> list = new ArrayList<>();
             sandro.lab
-            michele.ar
+			for (final Map.Entry<String, Object> e : rowFields.entrySet()) {
-            sandro.lab
+				if (!fields.isEmpty()) {
 					fields += ",";
+				}
 				fields += e.getKey();
 				if (!values.isEmpty()) {
 					values += ",";
+				}
 				values += "?";
 				list.add(e.getValue());
+			}
 			int count = 0;
-            claudio.at
+			if (rowFields.containsKey(DNET_RESOURCE_ID_FIELD)) {
-            michele.ar
+				final List<Object> list2 = new ArrayList<>();
-            sandro.lab
+				list2.addAll(list);
-            claudio.at
+				list2.add(rowFields.get(DNET_RESOURCE_ID_FIELD));
 				count =
-            michele.ar
+					jdbcTemplate.update("UPDATE " + table + " SET (" + fields + ") = (" + values + ") WHERE " + DNET_RESOURCE_ID_FIELD + "=?", list2.toArray());
             sandro.lab
 			if (count == 0) {
-            claudio.at
+				jdbcTemplate.update("INSERT INTO " + table + " (" + fields + ") VALUES (" + values + ")", list.toArray());
             sandro.lab
 		} catch (final Exception e) {
 			throw new DatabaseException("Error adding or updating record", e);
+		}
+	}
-            claudio.at
+	protected void deleteRow(final JdbcTemplate jdbcTemplate, final String table, final Map<String, Object> rowFields) throws DatabaseException {
-            sandro.lab
+		if (log.isDebugEnabled()) {
-            claudio.at
+			log.debug("Deleting element from table " + table);
             sandro.lab
-            claudio.at
+		verifyParameters(table);
 		verifyParameters(rowFields.keySet().toArray(new String[rowFields.size()]));
             sandro.lab
-            michele.ar
+		final List<Object> list = new ArrayList<>();
             sandro.lab
 		String where = "";
-            michele.ar
+		for (final Map.Entry<String, Object> e : rowFields.entrySet()) {
-            sandro.lab
+			if (!where.isEmpty()) {
 				where += " AND ";
+			}
 			where += e.getKey() + "=?";
 			list.add(e.getValue());
+		}
-            michele.ar
+		if (where.isEmpty()) { throw new DatabaseException("Delete condition is empty"); }
 		final int n = jdbcTemplate.update("DELETE FROM " + table + " WHERE " + where, list.toArray());
             sandro.lab
 		if (log.isDebugEnabled()) {
 			log.debug("Number of Deleted records: " + n);
+		}
+	}
 	public void deleteRowByResourceId(final String database, final String table, final String resourceIdentifier) throws DatabaseException {
 		verifyParameters(database, table, resourceIdentifier);
-            claudio.at
+		final JdbcTemplate jdbcTemplate = getJdbcTemplate(database);
-            alessia.ba
+		jdbcTemplate.update("DELETE FROM " + table + " WHERE " + DNET_RESOURCE_ID_FIELD + "=?", resourceIdentifier);
             sandro.lab
 	public void clearTable(final String database, final String table) throws DatabaseException {
 		verifyParameters(database, table);
-            claudio.at
+		final JdbcTemplate jdbcTemplate = getJdbcTemplate(database);
-            sandro.lab
+		jdbcTemplate.update("DELETE FROM " + table);
+	}
 	public void prepareManagementOfTable(final String database, final String table) throws DatabaseException {
 		verifyParameters(database, table);
-            claudio.at
+		final JdbcTemplate jdbcTemplate = getJdbcTemplate(database);
             sandro.lab
 		if (!isManagedTable(jdbcTemplate, table)) {
 			jdbcTemplate.update(getSQLFromTemplate("manageTable", database, table, null));
 			log.info("Added management of table " + table);
+		}
+	}
 	public void removeManagementOfTable(final String database, final String table) throws DatabaseException {
 		verifyParameters(database, table);
-            claudio.at
+		final JdbcTemplate jdbcTemplate = getJdbcTemplate(database);
             sandro.lab
 		if (isManagedTable(jdbcTemplate, table)) {
 			jdbcTemplate.update(getSQLFromTemplate("unmanageTable", database, table, null));
 			log.info("Removed management of table " + table);
+		}
+	}
 	public boolean isManagedTable(final String database, final String table) throws DatabaseException {
 		verifyParameters(database, table);
-            claudio.at
+		final JdbcTemplate jdbcTemplate = getJdbcTemplate(database);
-            sandro.lab
+		return isManagedTable(jdbcTemplate, table);
+	}
 	private boolean isManagedTable(final JdbcTemplate jdbcTemplate, final String table) {
-            michele.ar
+		return jdbcTemplate
 			.queryForObject("SELECT count(*) FROM information_schema.columns WHERE table_name = ? AND column_name = ?", Integer.class, table, DNET_RESOURCE_ID_FIELD) == 1;
             sandro.lab
-            claudio.at
+	public boolean isLoggedTable(final String database, final String table) throws DatabaseException {
 		verifyParameters(database, table);
-            claudio.at
+		final JdbcTemplate jdbcTemplate = getJdbcTemplate(database);
-            claudio.at
+		return isLoggedTable(jdbcTemplate, table);
+	}
 	private boolean isLoggedTable(final JdbcTemplate jdbcTemplate, final String table) {
-            michele.ar
+		return jdbcTemplate.queryForObject("SELECT count(*) FROM information_schema.tables WHERE table_name = ?", Integer.class, table + "_log") == 1;
             claudio.at
-            sandro.lab
+	public String getDefaultDnetIdentifier(final String database, final String table) throws DatabaseException {
 		verifyParameters(database, table);
-            claudio.at
+		final JdbcTemplate jdbcTemplate = getJdbcTemplate(database);
-            michele.ar
+		if (isManagedTable(jdbcTemplate, table)) {
 			return jdbcTemplate.queryForObject("SELECT column_default FROM information_schema.columns WHERE table_name = ? AND column_name = ?", new Object[] {
 				table, DNET_RESOURCE_ID_FIELD
 			}, String.class);
+		}
-            sandro.lab
+		return "";
+	}
 	public void reassignDefaultDnetIdentifiers(final String db) throws DatabaseException {
-            michele.ar
+		for (final String t : listCommonDBTables(db)) {
-            sandro.lab
+			reassignDefaultDnetIdentifiers(db, t);
+		}
+	}
 	public void reassignDefaultDnetIdentifiers(final String db, final String t) throws DatabaseException {
-            michele.ar
+		if (!isManagedTable(db, t)) { return; }
             sandro.lab
-            michele.ar
+		final SqlRowSet rows =
 			executeSql(db, "SELECT pg_attribute.attname as pkey FROM pg_index, pg_class, pg_attribute " + "WHERE pg_class.oid = '" + t + "'::regclass "
 				+ "AND indrelid = pg_class.oid " + "AND pg_attribute.attrelid = pg_class.oid "
 				+ "AND pg_attribute.attnum = any(pg_index.indkey) AND indisprimary " + "ORDER BY pkey", SqlRowSet.class);
             sandro.lab
 		String defaultValue = "";
 		while (rows.next()) {
 			if (!defaultValue.isEmpty()) {
 				defaultValue += "||'@@'||";
+			}
 			defaultValue += rows.getString("pkey");
+		}
 		executeSql(db, "UPDATE " + t + " SET " + DatabaseUtils.DNET_RESOURCE_ID_FIELD + " = " + defaultValue);
 		log.info("Reassigned dnetId for table " + t);
+	}
 	public String getSQLFromTemplate(final String sqlTemplate, final String db, final String table, Map<String, Object> map) {
 		if (map == null) {
-            michele.ar
+			map = new HashMap<>();
             sandro.lab
 		map.put("mainDB", defaultDB);
 		map.put("db", db);
 		map.put("table", table);
 		map.put("idField", DNET_RESOURCE_ID_FIELD);
 		return VelocityEngineUtils.mergeTemplateIntoString(velocityEngine, "eu/dnetlib/enabling/database/velocity/" + sqlTemplate + ".sql.vm", "UTF-8", map);
+	}
 	public List<GenericRow> obtainListOfRows(final String xml) throws DatabaseException {
 		try {
-            michele.ar
+			final Document doc = new SAXReader().read(new StringReader(xml));
             sandro.lab
-            michele.ar
+			final List<GenericRow> list = new ArrayList<>();
             sandro.lab
-            michele.ar
+			for (final Object or : doc.selectNodes("//ROW")) {
 				final Element row = (Element) or;
             sandro.lab
-            michele.ar
+				final String table = row.valueOf("@table");
             sandro.lab
-            michele.ar
+				if (table == null || table.isEmpty()) { throw new DatabaseException("Attribute table is missing in XSLT"); }
             sandro.lab
-            michele.ar
+				final boolean toDelete = "deleted".equals(row.valueOf("@status"));
             sandro.lab
-            michele.ar
+				final Map<String, Object> fields = new HashMap<>();
             sandro.lab
-            michele.ar
+				for (final Object of : row.selectNodes("./FIELD")) {
 					final Node node = (Node) of;
 					final String key = node.valueOf("@name");
 					final String type = node.valueOf("@type");
 					final String format = node.valueOf("@format");
 					final String valueS = node.getText().trim();
             sandro.lab
-            michele.ar
+					if (key != null && !key.isEmpty()) {
             sandro.lab
-            michele.ar
+						final Object value;
 						if (type == null || type.isEmpty()) {
 							// String types (text, char(), varchar())
 							value = valueS;
 						} else {
-            sandro.lab
+							try {
-            michele.ar
+								// type array should be evaluated before the null object
 								if (type.equals("array")) {
 									final List<String> arrayItems = new ArrayList<>();
 									for (final Object oi : node.selectNodes("./ITEM")) {
 										arrayItems.add(((Node) oi).getText());
+									}
 									value = new SqlTextArrayValue(arrayItems.toArray(new String[arrayItems.size()]));
 								} else if ("".equals(valueS)) {
 									// an empty string in a typed field means null
-            sandro.lab
+									value = null;
 								} else if (type.equals("int")) {
 									value = Integer.parseInt(valueS);
 								} else if (type.equals("float")) {
 									value = Float.parseFloat(valueS);
 								} else if (type.equals("boolean")) {
-            claudio.at
+									value = parseBoolean(valueS);
-            sandro.lab
+								} else if (type.equals("date")) {
 									value = parseDate(valueS, format);
 								} else if (type.equals("iso8601Date")) {
-            michele.ar
+									final DateTime date = ISODateTimeFormat.dateTimeParser().parseDateTime(valueS);
-            sandro.lab
+									value = date.toDate();
 									// value = new DateUtils().parse(valueS);
-            michele.ar
+								} else {
 									log.error("Invalid type: " + type);
 									throw new DatabaseException("Invalid type: " + type);
             sandro.lab
-            michele.ar
+							} catch (final IllegalArgumentException e) {
-            sandro.lab
+								log.fatal("cannot convert '" + valueS + "' to " + type, e);
 								throw e;
+							}
+						}
 						fields.put(key, value);
+					}
+				}
-            claudio.at
+				list.add(new GenericRow(table, fields, toDelete));
             sandro.lab
 			return list;
-            michele.ar
+		} catch (final Exception e) {
-            sandro.lab
+			log.error("Error obtaining list of rows from xml: " + xml);
 			throw new DatabaseException(e);
+		}
+	}
             claudio.at
-            michele.ar
+	protected boolean parseBoolean(final String s) {
-            michele.ar
+		if (TRUE_VALUES.contains(s.toLowerCase().trim())) { return true; }
 		if (FALSE_VALUES.contains(s.toLowerCase().trim())) { return false; }
             claudio.at
 		return BooleanUtils.toBoolean(s);
+	}
-            sandro.lab
+	public void setManaged(final String dbName, final boolean managed) throws DatabaseException {
 		verifyParameters(dbName);
-            michele.ar
+		final JdbcTemplate jdbcTemplate = getJdbcTemplate(dbName);
-            sandro.lab
+		if (managed) {
 			jdbcTemplate.update("COMMENT ON DATABASE " + dbName + " IS 'isManaged'");
 		} else {
 			jdbcTemplate.update("COMMENT ON DATABASE " + dbName + " IS NULL");
+		}
+	}
             claudio.at
-            sandro.lab
+	// public for testing
 	public Date parseDate(final String date, String format) {
-            michele.ar
+		if (format == null || format.isEmpty()) {
-            sandro.lab
+			format = SQL_DATE_FORMAT;
+		}
 		try {
-            michele.ar
+			final java.util.Date parsed = new SimpleDateFormat(format).parse(date);
 			final String ret = new SimpleDateFormat(SQL_DATE_FORMAT).format(parsed);
-            sandro.lab
+			return Date.valueOf(ret);
-            michele.ar
+		} catch (final ParseException e) {
-            sandro.lab
+			return null;
+		}
+	}
-            claudio.at
+	private JdbcTemplate getJdbcTemplate(final String dbName) {
-            michele.ar
+		final JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(dbName);
-            claudio.at
+		jdbcTemplate.setFetchSize(getFetchSize());
 		return jdbcTemplate;
+	}
-            sandro.lab
+	@Required
 	public void setVelocityEngine(final VelocityEngine velocityEngine) {
 		this.velocityEngine = velocityEngine;
+	}
-            alessia.ba
+	public String getDbPrefix() {
 		return dbPrefix;
+	}
-            sandro.lab
+	@Required
 	public void setDbPrefix(final String dbPrefix) {
 		this.dbPrefix = dbPrefix;
+	}
 	public DataSourceFactory getDataSourceFactory() {
 		return dataSourceFactory;
+	}
 	@Required
 	public void setDataSourceFactory(final DataSourceFactory dataSourceFactory) {
 		this.dataSourceFactory = dataSourceFactory;
+	}
 	public JdbcTemplateFactory getJdbcTemplateFactory() {
 		return jdbcTemplateFactory;
+	}
-            alessia.ba
+	@Required
 	public void setJdbcTemplateFactory(final JdbcTemplateFactory jdbcTemplateFactory) {
 		this.jdbcTemplateFactory = jdbcTemplateFactory;
+	}
-            sandro.lab
+	public TransactionTemplateFactory getTransactionTemplateFactory() {
 		return transactionTemplateFactory;
+	}
 	@Required
 	public void setTransactionTemplateFactory(final TransactionTemplateFactory transactionTemplateFactory) {
 		this.transactionTemplateFactory = transactionTemplateFactory;
+	}
 	public int getNumbersOfRecordsForTransaction() {
 		return numbersOfRecordsForTransaction;
+	}
 	@Required
 	public void setNumbersOfRecordsForTransaction(final int numbersOfRecordsForTransaction) {
 		this.numbersOfRecordsForTransaction = numbersOfRecordsForTransaction;
+	}
 	public String getDefaultDB() {
 		return defaultDB;
+	}
 	@Required
-            claudio.at
+	public void setDefaultDB(final String defaultDB) {
-            sandro.lab
+		this.defaultDB = defaultDB;
+	}
-            claudio.at
+	public int getFetchSize() {
 		return fetchSize;
+	}
 	public void setFetchSize(final int fetchSize) {
 		this.fetchSize = fetchSize;
+	}
-            claudio.at
+	public class TableDates {
-            claudio.at
+		private java.sql.Date lastInsert;
 		private java.sql.Date lastUpdate;
 		private java.sql.Date lastDelete;
             claudio.at
-            claudio.at
+		public java.sql.Date getLastInsert() {
-            claudio.at
+			return lastInsert;
+		}
 		public void setLastInsert(final Date lastInsert) {
 			this.lastInsert = lastInsert;
+		}
 		public Date getLastUpdate() {
 			return lastUpdate;
+		}
 		public void setLastUpdate(final Date lastUpdate) {
 			this.lastUpdate = lastUpdate;
+		}
 		public Date getLastDelete() {
 			return lastDelete;
+		}
 		public void setLastDelete(final Date lastDelete) {
 			this.lastDelete = lastDelete;
+		}
+	}
             sandro.lab

Project

General

Profile

D-Net