D-Net

package eu.dnetlib.enabling.database.utils;

import java.io.StringReader;

import java.sql.Array;

import java.sql.Date;

import java.sql.ResultSetMetaData;

import java.text.ParseException;

import java.text.SimpleDateFormat;

import java.util.*;

import java.util.concurrent.ArrayBlockingQueue;

import java.util.concurrent.BlockingQueue;

import java.util.concurrent.Executors;

import java.util.concurrent.TimeUnit;

import java.util.concurrent.atomic.AtomicReference;

import java.util.regex.Pattern;

import javax.sql.DataSource;

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;

import org.apache.commons.lang.BooleanUtils;

import org.apache.commons.lang.StringUtils;

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;

public class DatabaseUtils {

	public static final String DNET_RESOURCE_ID_FIELD = "_dnet_resource_identifier_";

	public static final int BLOCKING_QUEUE_TIMEOUT = 300;

	private static final String SQL_DATE_FORMAT = "yyyy-MM-dd";

	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");

	private DataSourceFactory dataSourceFactory;

	private JdbcTemplateFactory jdbcTemplateFactory;

	private TransactionTemplateFactory transactionTemplateFactory;

	private String defaultDB;

	private VelocityEngine velocityEngine;

	private String dbPrefix;

	private int numbersOfRecordsForTransaction;

	public List<String> listCommonDBTables(final String database) throws DatabaseException {

		String query =

				"SELECT table_name FROM information_schema.tables " + "WHERE table_schema = 'public' " + "AND table_type != 'VIEW' "

						+ "AND table_name NOT LIKE '%_log'";

		return getTypedListFromSql(database, query, String.class);

	}

	public List<String> listCommonDBViews(final String database) throws DatabaseException {

		String query =

				"SELECT table_name FROM information_schema.tables " + "WHERE table_schema = 'public' " + "AND table_type = 'VIEW' "

						+ "AND table_name NOT LIKE '%_log'";

		return getTypedListFromSql(database, query, String.class);

	}

	public List<DnetDatabase> listAllDatabases() throws DatabaseException {

		final String query =

				"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";

		final JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(defaultDB);

		final List<DnetDatabase> list = Lists.newArrayList();

		for (Map<String, Object> map : jdbcTemplate.queryForList(query)) {

			list.add(new DnetDatabase(map.get("db").toString(), Boolean.parseBoolean(map.get("managed").toString())));

		}

		return list;

	}

	public <T> List<T> getTypedListFromSql(final String dbName, final String query, final Class<T> clazz) throws DatabaseException {

		JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(dbName);

		try {

			List<T> list = new ArrayList<T>();

			for (Object obj : jdbcTemplate.queryForList(query, clazz)) {

				list.add(clazz.cast(obj));

			}

			return list;

		} catch (DataAccessException e) {

			throw new DatabaseException(e);

		}

	}

	public List<String> getSimpleListFromSql(final String dbName, final String query) throws DatabaseException {

		JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(dbName);

		try {

			List<String> list = new ArrayList<>();

			for (Object obj : jdbcTemplate.queryForList(query)) {

				list.add(obj.toString());

			}

			return list;

		} catch (DataAccessException e) {

			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 {

		final JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(dbName);

		try {

			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 if (clazz == BlockingQueue.class) {

				log.debug("Creating Queue");

				final ArrayBlockingQueue<Map<String, Object>> q = Queues.newArrayBlockingQueue(BLOCKING_QUEUE_SIZE);

				Executors.newSingleThreadExecutor().submit(() -> {

					try {

						jdbcTemplate.query(query, rs -> {

							ResultSetMetaData md = rs.getMetaData();

							Map<String, Object> row = new HashMap<>();

							for (int i = 1; i <= md.getColumnCount(); i++) {

								row.put(md.getColumnName(i), rs.getObject(i));

							}

							try {

								if (!rs.isClosed() && !q.offer(row, BLOCKING_QUEUE_TIMEOUT, TimeUnit.SECONDS)) {

									log.warn("The consumer doesn't consume my queue, I stop");

									rs.close();

									return;

								}

								log.debug("Putted element in queue");

							} catch (InterruptedException e) {

								log.error("Error putting element in queue");

								throw new RuntimeException(e);

							}

						});

					} catch (Throwable e) {

						log.error("Exception executing SQL", e);

						throw new RuntimeException(e);

					}

					try {

						// An empty Map indicates the end of the resultset

						q.offer(new HashMap<>(), BLOCKING_QUEUE_TIMEOUT, TimeUnit.SECONDS);

					} catch (InterruptedException e) {

						log.error("Error putting LAST element in queue");

						throw new RuntimeException(e);

					}

					log.debug(" -- End of Sql Resultset");

				});

				log.debug("Returned Queue");

				return (T) q;

			} else {

				jdbcTemplate.update(query);

				return null;

			}

		} catch (Throwable e) {

			throw new DatabaseException(e);

		}

	}

	public boolean contains(final String db, final String table, final String column, final String value) throws DatabaseException {

		String query = "";

		try {

			verifyParameters(db, table, column);

			query = "SELECT " + column + " FROM " + table + " WHERE " + column + " = '" + value + "'";

			List<String> res = getSimpleListFromSql(db, query);

			return res != null && res.size() > 0;

		} catch (Throwable e) {

			throw new DatabaseException("Error performing SQL: " + query, e);

		}

	}

	public List<Map<?, ?>> describeTable(final String database, final String table) throws DatabaseException {

		verifyParameters(database, table);

		try {

			JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(database);

			List<Map<?, ?>> response = new ArrayList<>();

			String query = "SELECT * FROM information_schema.columns WHERE table_name = ?";

			for (Object o : jdbcTemplate.queryForList(query, new Object[] { table })) {

				if (o instanceof Map<?, ?>) {

					response.add((Map<?, ?>) o);

				}

			}

			return response;

		} catch (DataAccessException e) {

			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 {

		Document doc = DocumentHelper.createDocument();

		Element root = doc.addElement("DB_TABLE");

		Element head = root.addElement("HEADER");

		head.addElement("DATABASE").addAttribute("value", db);

		head.addElement("TABLE").addAttribute("value", t);

		head.addElement("DATE").addAttribute("value", DateUtils.now_ISO8601());

		Element body = root.addElement("BODY");

		for (Document d : dumpTableAsList(db, t)) {

			body.add(d.getRootElement());

		}

		return doc;

	}

	public List<Document> dumpTableAsList(final String db, final String t) throws DatabaseException {

		JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(db);

		List<Document> list = new ArrayList<Document>();

		for (Object o : jdbcTemplate.queryForList("SELECT * FROM " + t)) {

			if (o instanceof Map<?, ?>) {

				list.add(rowToDocument((Map<?, ?>) o));

			}

		}

		return list;

	}

	public Document rowToDocument(final Map<?, ?> map) throws DatabaseException {

		Document doc = DocumentHelper.createDocument();

		Element row = doc.addElement("ROW");

		for (Map.Entry<?, ?> entry : map.entrySet()) {

			Element col = row.addElement("FIELD");

			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);

		JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(database);

		String query = "SELECT * FROM " + table + " WHERE " + DNET_RESOURCE_ID_FIELD + "=?";

		Map<?, ?> map = jdbcTemplate.queryForMap(query, resourceId);

		Document doc = DocumentHelper.createDocument();

		Element root = doc.addElement("DB_RECORD");

		Element head = root.addElement("HEADER");

		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());

		Element body = root.addElement("BODY");

		Element row = body.addElement("ROW");

		for (Map.Entry<?, ?> entry : map.entrySet()) {

			Element col = row.addElement("FIELD");

			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 {

				for (Object o : (Object[]) ((Array) value).getArray()) {

					addValue(elem.addElement("ITEM"), o);

				}

			} catch (Exception e) {

				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 {

		Pattern pattern = Pattern.compile("\\w{1,128}");

		for (String p : params) {

			log.debug("TESTING SQL PARAM:" + p);

			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 {

				log.debug("TEST OK");

			}

		}

	}

	public void importFromIterable(final String db, final Iterable<String> iterable) throws DatabaseException {

		verifyParameters(db);

		final DataSource dataSource = dataSourceFactory.createDataSource(db);

		final JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(dataSource);

		final TransactionTemplate transactionTemplate = transactionTemplateFactory.createTransactionTemplate(dataSource);

		int counterTotal = 0;

		long start = DateUtils.now();

		final List<GenericRow> rows = new ArrayList<>();

		for (String s : iterable) {

			rows.addAll(obtainListOfRows(s));

			if (rows.size() > numbersOfRecordsForTransaction) {

				counterTotal += rows.size();

				importTransaction(jdbcTemplate, transactionTemplate, rows);

				rows.clear();

			}

		}

		counterTotal += rows.size();

		importTransaction(jdbcTemplate, transactionTemplate, rows);

		long end = DateUtils.now();

		log.info("**********************************************************");

		log.info("Processed " + counterTotal + " rows in " + ((end - start) / 1000) + " seconds");

		log.info("**********************************************************");

	}

	private void importTransaction(final JdbcTemplate jdbcTemplate, final TransactionTemplate transactionTemplate, List<GenericRow> rows)

			throws DatabaseException {

		if (rows != null && rows.size() > 0) {

			importTransactionInternal(jdbcTemplate, transactionTemplate, rows);

		}

	}

	private List<GenericRow> importTransactionInternal(final JdbcTemplate jdbcTemplate,

			final TransactionTemplate transactionTemplate,

			final List<GenericRow> rows) throws DatabaseException {

		final AtomicReference<DatabaseException> error = new AtomicReference<>();

		try {

			return transactionTemplate.execute(status -> {

				final List<GenericRow> ok = Lists.newArrayList();

				try {

					for (GenericRow row : rows) {

						if (row.isToDelete()) {

							deleteRow(jdbcTemplate, row);

						} else {

							addOrUpdateRow(jdbcTemplate, row);

						}

						ok.add(row);

					}

				} catch (DatabaseException e) {

					log.warn("Transaction failed", e);

					status.setRollbackOnly();

					error.set(e);

				}

				return ok;

			});

		} finally {

			if (error.get() != null) {

				throw error.get();

			}

		}

	}

	protected void addOrUpdateRow(final JdbcTemplate jdbcTemplate, final GenericRow row) throws DatabaseException {

		try {

			if (log.isDebugEnabled()) {

				log.debug("Adding or updating element to table " + row.getTable());

			}

			verifyParameters(row.getTable());

			verifyParameters(row.getFields().keySet().toArray(new String[row.getFields().size()]));

			final List<Map<String, Object>> res =

					jdbcTemplate.queryForList("SELECT 1 FROM datasources d WHERE d.id = ? and d.managed is true", row.getDatasourceId());

			if (res != null && !res.isEmpty()) {

				log.debug(String.format("avoiding to update managed datasource %s", row.getDatasourceId()));

				return;

			}

			String fields = "";

			String values = "";

			List<Object> list = new ArrayList<>();

			for (Map.Entry<String, Object> e : row.getFields().entrySet()) {

				if (!fields.isEmpty()) {

					fields += ",";

				}

				fields += e.getKey();

				if (!values.isEmpty()) {

					values += ",";

				}

				values += "?";

				list.add(e.getValue());

			}

			int count = 0;

			if (row.getFields().containsKey(DNET_RESOURCE_ID_FIELD)) {

				List<Object> list2 = new ArrayList<>();

				list2.addAll(list);

				list2.add(row.getFields().get(DNET_RESOURCE_ID_FIELD));

				count = jdbcTemplate.update(String.format("UPDATE %s SET (%s) = (%s) WHERE %s = ?", row.getTable(), fields, values, DNET_RESOURCE_ID_FIELD), list2.toArray());

			}

			if (count == 0) {

				jdbcTemplate.update(String.format("INSERT INTO %s (%s) VALUES (%s)", row.getTable(), fields, values), list.toArray());

			}

		} catch (final Exception e) {

			throw new DatabaseException("Error adding or updating record", e);

		}

	}

	protected void deleteRow(final JdbcTemplate jdbcTemplate, final GenericRow row) throws DatabaseException {

		if (log.isDebugEnabled()) {

			log.debug("Deleting element from table " + row.getTable());

		}

		verifyParameters(row.getTable());

		verifyParameters(row.getFields().keySet().toArray(new String[row.getFields().size()]));

		List<Object> list = new ArrayList<>();

		String where = "";

		for (Map.Entry<String, Object> e : row.getFields().entrySet()) {

			if (!where.isEmpty()) {

				where += " AND ";

			}

			where += e.getKey() + "=?";

			list.add(e.getValue());

		}

		if (where.isEmpty()) throw new DatabaseException("Delete condition is empty");

		int n = jdbcTemplate.update("DELETE FROM " + row.getTable() + " WHERE " + where, list.toArray());

		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);

		JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(database);

		jdbcTemplate.update("DELETE FROM " + table + " WHERE " + DNET_RESOURCE_ID_FIELD + "=?", resourceIdentifier);

	}

	public void clearTable(final String database, final String table) throws DatabaseException {

		verifyParameters(database, table);

		JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(database);

		jdbcTemplate.update("DELETE FROM " + table);

	}

	public void prepareManagementOfTable(final String database, final String table) throws DatabaseException {

		verifyParameters(database, table);

		JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(database);

		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);

		JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(database);

		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);

		JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(database);

		return isManagedTable(jdbcTemplate, table);

	}

	private boolean isManagedTable(final JdbcTemplate jdbcTemplate, final String table) {

		return jdbcTemplate.queryForObject("SELECT count(*) FROM information_schema.columns WHERE table_name = ? AND column_name = ?", Integer.class,

				table, DNET_RESOURCE_ID_FIELD) == 1;

	}

	public String getDefaultDnetIdentifier(final String database, final String table) throws DatabaseException {

		verifyParameters(database, table);

		JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(database);

		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);

		return "";

	}

	public void reassignDefaultDnetIdentifiers(final String db) throws DatabaseException {

		for (String t : listCommonDBTables(db)) {

			reassignDefaultDnetIdentifiers(db, t);

		}

	}

	public void reassignDefaultDnetIdentifiers(final String db, final String t) throws DatabaseException {

		if (!isManagedTable(db, t)) return;

		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);

		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) {

			map = new HashMap<>();

		}

		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 {

			final Document doc = new SAXReader().read(new StringReader(xml));

			final String datasourceId = doc.valueOf("//ROWS/ROW[@table='datasources']/FIELD[@name='id']/text()");

			if (StringUtils.isBlank(datasourceId)) {

				throw new DatabaseException("datasource Id is missing, check your mappings!");

			}

			final List<GenericRow> list = Lists.newArrayList();

			for (Object or : doc.selectNodes("//ROW")) {

				Element row = (Element) or;

				String table = row.valueOf("@table");

				if ((table == null) || table.isEmpty()) throw new DatabaseException("Attribute table is missing in XSLT");

				boolean toDelete = "deleted".equals(row.valueOf("@status"));

				Map<String, Object> fields = new HashMap<String, Object>();

				for (Object of : row.selectNodes("./FIELD")) {

					Node node = (Node) of;

					String key = node.valueOf("@name");

					String type = node.valueOf("@type");

					String format = node.valueOf("@format");

					String valueS = node.getText().trim();

					if ((key != null) && !key.isEmpty()) {

						Object value = valueS;

						if (type != null) {

							try {

								// probably an empty string in a typed field means null

								if ("".equals(valueS)) {

									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")) {

									value = parseBoolean(valueS);

								} else if (type.equals("date")) {

									value = parseDate(valueS, format);

								} else if (type.equals("iso8601Date")) {

									DateTime date = ISODateTimeFormat.dateTimeParser().parseDateTime(valueS);

									value = date.toDate();

									// value = new DateUtils().parse(valueS);

								}

							} catch (IllegalArgumentException e) {

								log.fatal("cannot convert '" + valueS + "' to " + type, e);

								throw e;

							}

						}

						fields.put(key, value);

					}

				}

				list.add(new GenericRow(datasourceId, table, fields, toDelete));

			}

			return list;

		} catch (Exception e) {

			log.error("Error obtaining list of rows from xml: " + xml);

			throw new DatabaseException(e);

		}

	}

	protected boolean parseBoolean(final String s) {

		if (TRUE_VALUES.contains(s.toLowerCase().trim())) return true;

		if (FALSE_VALUES.contains(s.toLowerCase().trim())) return false;

		return BooleanUtils.toBoolean(s);

	}

	public void setManaged(final String dbName, final boolean managed) throws DatabaseException {

		verifyParameters(dbName);

		JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(dbName);

		if (managed) {

			jdbcTemplate.update("COMMENT ON DATABASE " + dbName + " IS 'isManaged'");

		} else {

			jdbcTemplate.update("COMMENT ON DATABASE " + dbName + " IS NULL");

		}

	}

	// public for testing

	public Date parseDate(final String date, String format) {

		if ((format == null) || format.isEmpty()) {

			format = SQL_DATE_FORMAT;

		}

		try {

			java.util.Date parsed = new SimpleDateFormat(format).parse(date);

			String ret = new SimpleDateFormat(SQL_DATE_FORMAT).format(parsed);

			return Date.valueOf(ret);

		} catch (ParseException e) {

			return null;

		}

	}

	@Required

	public void setVelocityEngine(final VelocityEngine velocityEngine) {

		this.velocityEngine = velocityEngine;

	}

	public String getDbPrefix() {

		return dbPrefix;

	}

	@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;

	}

	@Required

	public void setJdbcTemplateFactory(final JdbcTemplateFactory jdbcTemplateFactory) {

		this.jdbcTemplateFactory = jdbcTemplateFactory;

	}

	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

	public void setDefaultDB(final String defaultDB) {

		this.defaultDB = defaultDB;

	}

}