package eu.dnetlib.enabling.database.utils;

import java.io.StringReader;
import java.sql.*;
import java.sql.Date;
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.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.core.RowCallbackHandler;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
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_";
	private 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;

	private int blockingQueueTimeout = BLOCKING_QUEUE_TIMEOUT;

	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 Map<String, TableDates> getTableDatesForDB(final String db) throws DatabaseException {
		Map<String, TableDates> res = new HashMap<String, TableDates>();

		for (String table : listCommonDBTables(db)) {
			try {
				TableDates dates = new TableDates();

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

				SqlRowSet srs = executeSql(db, query, SqlRowSet.class);
				if (srs.next()) {
					dates.setLastInsert(srs.getDate("lastinsert"));
					dates.setLastUpdate(srs.getDate("lastupdate"));
					dates.setLastDelete(srs.getDate("lastdelete"));
				}
				res.put(table, dates);
			} catch (Exception e) {
				log.warn("Error obtaing dates for table " + table, e);
			}
		}
		return res;
	}

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

				Runnable run = new Runnable() {

					@Override
					public void run() {
						try {
							jdbcTemplate.query(query, getRowCallback(q));
						} 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<String, Object>(), getBlockingQueueTimeout(), TimeUnit.SECONDS);
						} catch (InterruptedException e) {
							log.error("Error putting LAST element in queue");
							throw new RuntimeException(e);
						}
						log.debug(" -- End of Sql Resultset");
					}

					private RowCallbackHandler getRowCallback(final BlockingQueue<Map<String, Object>> q) {
						return new RowCallbackHandler() {

							@Override
							public void processRow(final ResultSet rs) throws SQLException {

								ResultSetMetaData md = rs.getMetaData();
								Map<String, Object> row = new HashMap<String, Object>();
								for (int i = 1; i <= md.getColumnCount(); i++) {
									row.put(md.getColumnName(i), rs.getObject(i));
								}
								try {
									if (!rs.isClosed() && !q.offer(row, getBlockingQueueTimeout(), 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);
								}
							}
						};
					}
				};
				Executors.newSingleThreadExecutor().submit(run);

				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<Map<?, ?>>();
			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();

		List<GenericRow> rows = new ArrayList<GenericRow>();
		for (String prof : iterable) {
			rows.addAll(obtainListOfRows(prof));
			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<DatabaseException>();

		try {
			return transactionTemplate.execute(new TransactionCallback<List<GenericRow>>() {

				@Override
				public List<GenericRow> doInTransaction(final TransactionStatus status) {
					final List<GenericRow> ok = Lists.newArrayList();
					try {
						for (GenericRow row : rows) {
							if (row.isToDelete()) {
								deleteRow(jdbcTemplate, row.getTable(), row.getFields());
							} else {
								addOrUpdateRow(jdbcTemplate, row.getTable(), row.getFields());
							}
							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 String table, final Map<String, Object> rowFields) throws DatabaseException {
		try {

			if (log.isDebugEnabled()) {
				log.debug("Adding or updating element to table " + table);
			}
			verifyParameters(table);
			verifyParameters(rowFields.keySet().toArray(new String[rowFields.size()]));

			String fields = "";
			String values = "";
			List<Object> list = new ArrayList<Object>();

			for (Map.Entry<String, Object> e : rowFields.entrySet()) {
				if (!fields.isEmpty()) {
					fields += ",";
				}
				fields += e.getKey();
				if (!values.isEmpty()) {
					values += ",";
				}
				values += "?";
				list.add(e.getValue());
			}

			int count = 0;
			if (rowFields.containsKey(DNET_RESOURCE_ID_FIELD)) {
				List<Object> list2 = new ArrayList<Object>();
				list2.addAll(list);
				list2.add(rowFields.get(DNET_RESOURCE_ID_FIELD));
				count =
						jdbcTemplate.update("UPDATE " + table + " SET (" + fields + ") = (" + values + ") WHERE " + DNET_RESOURCE_ID_FIELD + "=?",
								list2.toArray());
			}
			if (count == 0) {
				jdbcTemplate.update("INSERT INTO " + table + " (" + fields + ") VALUES (" + values + ")", list.toArray());
			}
		} catch (final Exception e) {
			throw new DatabaseException("Error adding or updating record", e);
		}
	}

	protected void deleteRow(final JdbcTemplate jdbcTemplate, final String table, final Map<String, Object> rowFields) throws DatabaseException {
		if (log.isDebugEnabled()) {
			log.debug("Deleting element from table " + table);
		}
		verifyParameters(table);
		verifyParameters(rowFields.keySet().toArray(new String[rowFields.size()]));

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

		String where = "";

		for (Map.Entry<String, Object> e : rowFields.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 " + table + " 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);
		}

		addLogTable(database, table);
	}

	public void addLogTable(final String database, final String table) throws DatabaseException {
		verifyParameters(database, table);
		JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(database);

		if (!isLoggedTable(jdbcTemplate, table)) {
			jdbcTemplate.update(getSQLFromTemplate("removeLogTable", database, table, null));
			jdbcTemplate.update(getSQLFromTemplate("addLogTable", database, table, null));
			log.info("Added logs 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);
		}
		removeLogTable(database, table);
	}

	public void removeLogTable(final String database, final String table) throws DatabaseException {
		verifyParameters(database, table);
		JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(database);

		if (isLoggedTable(jdbcTemplate, table)) {
			jdbcTemplate.update(getSQLFromTemplate("removeLogTable", database, table, null));
			log.info("Removed logs 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 boolean isLoggedTable(final String database, final String table) throws DatabaseException {
		verifyParameters(database, table);
		JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(database);
		return isLoggedTable(jdbcTemplate, table);
	}

	private boolean isLoggedTable(final JdbcTemplate jdbcTemplate, final String table) {
		return jdbcTemplate.queryForObject("SELECT count(*) FROM information_schema.tables WHERE table_name = ?", Integer.class,
				table + "_log") == 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<String, Object>();
		}

		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 {
			Document doc = new SAXReader().read(new StringReader(xml));

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

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

	public int getBlockingQueueTimeout() {
		return blockingQueueTimeout;
	}

	public void setBlockingQueueTimeout(final int blockingQueueTimeout) {
		this.blockingQueueTimeout = blockingQueueTimeout;
	}

	public class TableDates {

		private Date lastInsert;
		private Date lastUpdate;
		private Date lastDelete;

		public Date getLastInsert() {
			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;
		}
	}

}