D-Net

{"type_source": "SVN", "goal": "package -U -T 4C source:jar", "url": "http://svn-public.driver.research-infrastructures.eu/driver/dnet45/modules/cnr-enabling-database-service/trunk/", "deploy_repository": "dnet45-snapshots", "version": "4", "mail": "sandro.labruzzo@isti.cnr.it,michele.artini@isti.cnr.it, claudio.atzori@isti.cnr.it, alessia.bardi@isti.cnr.it", "deploy_repository_url": "http://maven.research-infrastructures.eu/nexus/content/repositories/dnet45-snapshots", "name": "cnr-enabling-database-service"}

package eu.dnetlib.enabling.database.utils;

import java.io.StringReader;

import java.sql.Connection;

import java.sql.Date;

import java.util.Arrays;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import javax.sql.DataSource;

import com.google.common.collect.Lists;

import com.google.common.collect.Maps;

import eu.dnetlib.enabling.database.DataSourceFactory;

import eu.dnetlib.enabling.database.TransactionTemplateFactory;

import eu.dnetlib.enabling.database.objects.DnetDatabase;

import org.apache.velocity.app.VelocityEngine;

import org.dom4j.Document;

import org.dom4j.DocumentHelper;

import org.dom4j.Element;

import org.dom4j.io.SAXReader;

import org.junit.Before;

import org.junit.Ignore;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.mockito.Mock;

import org.mockito.runners.MockitoJUnitRunner;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.transaction.support.TransactionTemplate;

import static org.junit.Assert.*;

import static org.mockito.Matchers.any;

import static org.mockito.Matchers.anyString;

import static org.mockito.Mockito.*;

@RunWith(MockitoJUnitRunner.class)

public class DatabaseUtilsTest {

	private static final String DB = "THE_DB";

	private static final String TABLE = "THE_TABLE";

	private static final String QUERY = "SELECT age FROM persons";

	private static final String RESOURCE_ID = "id1234";

	private static final String DB_PREFIX = "THE_";

	public static final String DNET_RESOURCE_ID_FIELD = "_dnet_resource_identifier_";

	private static final String DEFAULT_DB = "postgres";

	// Class Under Test

	private DatabaseUtils dbUtils;

	private final List<Map<String, Object>> RESULTS = Lists.newArrayList();

	private Map<String, Object> MAP;

	private final DnetDatabase DNET_DB = new DnetDatabase();

	@Mock

	private JdbcTemplateFactory jdbcTemplateFactory;

	@Mock

	private JdbcTemplate jdbcTemplate;

	@Mock

	private DataSource dataSource;

	@Mock

	private VelocityEngine velocityEngine;

	@Mock

	private DataSourceFactory dataSourceFactory;

	@Mock

	private TransactionTemplateFactory transactionTemplateFactory;

	@Mock

	private TransactionTemplate transactionTemplate;

	@Mock

	private Connection connection;

	@SuppressWarnings("unchecked")

	@Before

	public void setUp() throws Exception {

		dbUtils = new DatabaseUtils();

		DNET_DB.setDbName(DB);

		MAP = new HashMap<String, Object>();

		MAP.put("k1", "v1");

		MAP.put("k2", "v2");

		MAP.put("k3", "v3");

		dbUtils.setJdbcTemplateFactory(jdbcTemplateFactory);

		dbUtils.setDataSourceFactory(dataSourceFactory);

		dbUtils.setTransactionTemplateFactory(transactionTemplateFactory);

		dbUtils.setVelocityEngine(velocityEngine);

		dbUtils.setDbPrefix(DB_PREFIX);

		dbUtils.setNumbersOfRecordsForTransaction(20);

		//when(jdbcTemplate.getDataSource()).thenReturn(dataSource);

		when(dataSourceFactory.createDataSource(anyString())).thenReturn(dataSource);

		//when(dataSource.getConnection()).thenReturn(connection);

		when(transactionTemplateFactory.createTransactionTemplate(dataSource)).thenReturn(transactionTemplate);

		when(jdbcTemplateFactory.createJdbcTemplate(DB)).thenReturn(jdbcTemplate);

		when(jdbcTemplate.queryForList(QUERY, Integer.class)).thenReturn(Arrays.asList(1, 2, 3, 4));

		for (int i = 0; i < 4; i++) {

			RESULTS.add(MAP);

		}

		when(jdbcTemplate.queryForList(QUERY)).thenReturn(RESULTS);

		when(jdbcTemplate.queryForList("SELECT * FROM " + TABLE)).thenReturn(Arrays.asList(MAP, MAP, MAP));

		when(jdbcTemplate.queryForList("SELECT * FROM information_schema.columns WHERE table_name = ?", new Object[] { TABLE })).thenReturn(

				Arrays.asList(MAP, MAP, MAP));

		//when(jdbcTemplate.queryForList("SELECT profileid FROM dnet_tables WHERE table_name='THE_TABLE' AND database_db_name='THE_DB'", String.class))

		//		.thenReturn(Arrays.asList("1234"));

	}

	@Test

	public void testListCommonDBTables() throws Exception {

		dbUtils.listCommonDBTables(DB);

		verify(jdbcTemplateFactory).createJdbcTemplate(DB);

		verify(jdbcTemplate).queryForList(

				"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type != 'VIEW' AND table_name NOT LIKE '%_log'",

				String.class);

	}

	@Test

	public void testListCommonDBViews() throws Exception {

		dbUtils.listCommonDBViews(DB);

		verify(jdbcTemplateFactory).createJdbcTemplate(DB);

		verify(jdbcTemplate).queryForList(

				"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'VIEW' AND table_name NOT LIKE '%_log'",

				String.class);

	}

	@Ignore

	@Test

	public void testListAllDatabases() throws Exception {

		dbUtils.listAllDatabases();

		verify(jdbcTemplateFactory).createJdbcTemplate(DEFAULT_DB);

		verify(jdbcTemplate).queryForList("SELECT datname FROM pg_database WHERE datname LIKE '" + DB_PREFIX + "%'", String.class);

	}

	@Test

	public void testGetTypedListFromSql() throws Exception {

		List<Integer> list = dbUtils.getTypedListFromSql(DB, QUERY, Integer.class);

		verify(jdbcTemplateFactory).createJdbcTemplate(DB);

		verify(jdbcTemplate).queryForList(QUERY, Integer.class);

		assertEquals(4, list.size());

	}

	@Test

	public void testGetSimpleListFromSql() throws Exception {

		List<String> list = dbUtils.getSimpleListFromSql(DB, QUERY);

		verify(jdbcTemplateFactory).createJdbcTemplate(DB);

		verify(jdbcTemplate).queryForList(QUERY);

		assertEquals(4, list.size());

	}

	@Test

	public void testExecuteSql() throws Exception {

		dbUtils.executeSql(DB, QUERY, null);

		verify(jdbcTemplateFactory).createJdbcTemplate(DB);

		verify(jdbcTemplate).update(QUERY);

	}

	@Test

	public void testDescribeTable() throws Exception {

		List<Map<?, ?>> res = dbUtils.describeTable(DB, TABLE);

		verify(jdbcTemplateFactory).createJdbcTemplate(DB);

		assertEquals(3, res.size());

		assertEquals(3, res.get(0).size());

		assertEquals(3, res.get(1).size());

		assertEquals(3, res.get(2).size());

	}

	@Test

	public void testDumpTableAsXML() throws Exception {

		String xml = dbUtils.dumpTableAsXML(DB, TABLE);

		verify(jdbcTemplateFactory).createJdbcTemplate(DB);

		assertNotNull(xml);

		assertTrue(xml.contains(TABLE));

		assertTrue(xml.contains(DB));

		assertTrue(xml.contains("ROW"));

	}

	@Test

	public void testDumpTableAsList() throws Exception {

		List<Document> list = dbUtils.dumpTableAsList(DB, TABLE);

		verify(jdbcTemplateFactory).createJdbcTemplate(DB);

		verify(jdbcTemplate).queryForList("SELECT * FROM " + TABLE);

		assertTrue(list.size() > 0);

	}

	@Test

	public void testGetRowByResourceId() throws Exception {

		dbUtils.getRowByResourceId(DB, TABLE, RESOURCE_ID);

		verify(jdbcTemplateFactory).createJdbcTemplate(DB);

		verify(jdbcTemplate).queryForMap("SELECT * FROM " + TABLE + " WHERE " + DatabaseUtils.DNET_RESOURCE_ID_FIELD + "=?", new Object[] { RESOURCE_ID });

	}

	@SuppressWarnings({ "unchecked", "rawtypes" })

	@Test

	public void testImportFromIterable() throws Exception {

		List<String> iterable = Lists.newArrayList("<ROWS><ROW table='t1' /></ROWS>", "<ROWS><ROW table='t2' /></ROWS>");

		GenericRow row1 = new GenericRow(TABLE, MAP, true);

		GenericRow row2 = new GenericRow(TABLE, MAP, false);;

		when(transactionTemplate.execute(any())).thenReturn(Lists.newArrayList(row1, row2, row1, row2));

		dbUtils.importFromIterable(DB, iterable);

		verify(jdbcTemplateFactory).createJdbcTemplate(DB);

		verify(transactionTemplateFactory).createTransactionTemplate(dataSource);

		verify(transactionTemplate, times(1)).execute(any());

	}

	@Test

	public void testDeleteRowByResourceId() throws Exception {

		dbUtils.deleteRowByResourceId(DB, TABLE, RESOURCE_ID);

		verify(jdbcTemplateFactory).createJdbcTemplate(DB);

		verify(jdbcTemplate).update("DELETE FROM " + TABLE + " WHERE " + DatabaseUtils.DNET_RESOURCE_ID_FIELD + "=?", new Object[] { RESOURCE_ID });

	}

	@Test

	public void testClearTable() throws Exception {

		dbUtils.clearTable(DB, TABLE);

		verify(jdbcTemplateFactory).createJdbcTemplate(DB);

		verify(jdbcTemplate).update("DELETE FROM " + TABLE);

	}

	@Ignore

	@SuppressWarnings("unchecked")

	@Test

	public void testPrepareManagementOfTable() throws Exception {

		when(jdbcTemplate.queryForObject(anyString(), any(Class.class), (Object[]) any())).thenReturn(1);

		dbUtils.prepareManagementOfTable(DB, TABLE);

		verify(jdbcTemplateFactory, atLeastOnce()).createJdbcTemplate(DB);

		verify(jdbcTemplate, atLeastOnce()).queryForObject(anyString(), Integer.class, (Object[]) any());

		verify(jdbcTemplate, never()).update(anyString());

	}

	@Ignore

	@SuppressWarnings("unchecked")

	@Test

	public void testPrepareManagementOfTable_2() throws Exception {

		when(jdbcTemplate.queryForObject(anyString(), any(Class.class), (Object[]) any())).thenReturn(0);

		dbUtils.prepareManagementOfTable(DB, TABLE);

		verify(jdbcTemplateFactory, atLeastOnce()).createJdbcTemplate(DB);

		verify(jdbcTemplate, atLeastOnce()).queryForObject(anyString(), Integer.class, (Object[]) any());

		verify(jdbcTemplate, atLeastOnce()).update(anyString());

	}

	@Ignore

	@SuppressWarnings("unchecked")

	@Test

	public void testClearManagementOfTable() throws Exception {

		when(jdbcTemplate.queryForObject(anyString(), any(Class.class), (Object[]) any())).thenReturn(0);

		dbUtils.removeManagementOfTable(DB, TABLE);

		verify(jdbcTemplateFactory, atLeastOnce()).createJdbcTemplate(DB);

		verify(jdbcTemplate, never()).update(anyString());

	}

	@Ignore

	@Test

	public void testClearManagementOfTable_2() throws Exception {

		when(jdbcTemplate.queryForObject(anyString(), any(Class.class), (Object[]) any())).thenReturn(1);

		dbUtils.removeManagementOfTable(DB, TABLE);

		verify(jdbcTemplateFactory, atLeastOnce()).createJdbcTemplate(DB);

		verify(jdbcTemplate, atLeastOnce()).update(anyString());

	}

	@Test

	public void testParseDate() throws Exception {

		Date d = dbUtils.parseDate("31-05-2011", "dd-MM-yyyy");

		assertEquals("2011-05-31", d.toString());

	}

	@Test

	public void testEscape() throws Exception {

		Map<String, String> m = Maps.newHashMap();

		m.put("url", "http://www.google.com/set?a=val1&b=val2");

		m.put("title", "My favourite movie title");

		Document doc = dbUtils.rowToDocument(m);

		assertNotNull(doc);

		Document doc2 = DocumentHelper.createDocument();

		Element elem = doc2.addElement("a");

		elem.setText(doc.asXML());

		doc2.setRootElement(elem);

		String tempXML = doc2.asXML();

		SAXReader reader = new SAXReader();

		Document doc3 = reader.read(new StringReader(tempXML));

		System.out.println(doc.asXML());;

		System.out.println(doc3.getRootElement().getText());;

		assertEquals(doc.asXML(), doc3.getRootElement().getText());

	}

	@Test

	public void testParseBoolean() throws Exception {

		assertEquals(dbUtils.parseBoolean("TRUE"), true);

		assertEquals(dbUtils.parseBoolean("true"), true);

		assertEquals(dbUtils.parseBoolean("T"), true);

		assertEquals(dbUtils.parseBoolean("Yes"), true);

		assertEquals(dbUtils.parseBoolean("false"), false);

		assertEquals(dbUtils.parseBoolean("F"), false);

		assertEquals(dbUtils.parseBoolean("No"), false);

		assertEquals(dbUtils.parseBoolean("N/A"), false);

		assertEquals(dbUtils.parseBoolean("XYZ"), false);

	}

}

package eu.dnetlib.enabling.database.utils;

import static org.junit.Assert.assertEquals;

import static org.mockito.Mockito.verify;

import static org.mockito.Mockito.when;

import javax.sql.DataSource;

import org.junit.Before;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.mockito.Mock;

import org.mockito.runners.MockitoJUnitRunner;

import org.springframework.jdbc.core.JdbcTemplate;

import eu.dnetlib.enabling.database.DataSourceFactory;

@RunWith(MockitoJUnitRunner.class)

public class JdbcTemplateFactoryTest {

	private static final String VALID_DB = "db01";

	// Class under test

	private JdbcTemplateFactory factory;

	@Mock

	private DataSourceFactory dataSourceFactory;

	@Mock

	private DataSource dataSource;

	@Before

	public void setUp() throws Exception {

		factory = new JdbcTemplateFactory();

		factory.setDataSourceFactory(dataSourceFactory);

		when(dataSourceFactory.createDataSource(VALID_DB)).thenReturn(dataSource);

	}

	@Test

	public void testCreateJdbcTemplate() {

		JdbcTemplate t = factory.createJdbcTemplate(VALID_DB);

		verify(dataSourceFactory).createDataSource(VALID_DB);

		assertEquals(dataSource, t.getDataSource());

	}

}

package eu.dnetlib.enabling.database;

import static org.junit.Assert.assertNotNull;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.junit.Before;

import org.junit.Ignore;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.mockito.runners.MockitoJUnitRunner;

@RunWith(MockitoJUnitRunner.class)

@Ignore

public class DataSourceFactoryImplTest {

	// Class under test

	private DataSourceFactoryImpl factory;

	@Before

	public void setUp() throws Exception {

		factory = new DataSourceFactoryImpl();

		factory.setBaseUrl("jdbc:postgresql://localhost:5432");

		factory.setDriverClassName("org.postgresql.Driver");

		factory.setUsername("dnet");

		factory.setPassword("dnetPwd");

	}

	@Test

	public void testCreateDataSource_1() throws SQLException {

		DataSource ds = factory.createDataSource("");

		assertNotNull(ds);

		assertNotNull(ds.getConnection());

	}

	@Test

	public void testCreateDataSource_2() throws SQLException {

		DataSource ds = factory.createDataSource("postgres");

		assertNotNull(ds);

		assertNotNull(ds.getConnection());

	}

	@Test(expected = SQLException.class)

	public void testCreateDataSource_error() throws SQLException {

		DataSource ds = factory.createDataSource("_INVALID_DATABASE_");

		assertNotNull(ds);

		assertNotNull(ds.getConnection());

	}

}

package eu.dnetlib.enabling.database;

import java.util.Date;

import eu.dnetlib.enabling.database.rmi.DatabaseException;

import org.junit.Before;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.mockito.Mock;

import org.mockito.runners.MockitoJUnitRunner;

import static org.mockito.Mockito.verify;

@RunWith(MockitoJUnitRunner.class)

public class DatabaseServiceImplTest {

	private static final String DBNAME = "THE_DB";

	private static final String TABLENAME = "THE_TABLE";

	// Class under test

	private DatabaseServiceImpl impl;

	@Mock

	private DatabaseServiceCore core;

	@Before

	public void setUp() throws Exception {

		impl = new DatabaseServiceImpl();

		impl.setCore(core);

	}

	@Test

	public void testDumpTable() throws DatabaseException {

		impl.dumpTable(DBNAME, TABLENAME);

		verify(core).generateResultSet(DBNAME, TABLENAME, null);

	}

	@Test

	public void testDumpTableAndLogs() throws DatabaseException {

		Date d1 = new Date(100000);

		Date d2 = new Date(200000);

		impl.dumpTableAndLogs(DBNAME, TABLENAME, d1, d2);

		verify(core).generateResultSet(DBNAME, TABLENAME, d1, d2);

	}

}

package eu.dnetlib.enabling.database.resultset;

import static org.junit.Assert.assertEquals;

import static org.mockito.Matchers.anyMap;

import static org.mockito.Matchers.anyString;

import static org.mockito.Matchers.eq;

import static org.mockito.Mockito.times;

import static org.mockito.Mockito.verify;

import static org.mockito.Mockito.when;

import java.util.List;

import org.dom4j.Document;

import org.junit.Before;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.mockito.Mock;

import org.mockito.runners.MockitoJUnitRunner;

import org.springframework.jdbc.support.rowset.SqlRowSet;

import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;

import eu.dnetlib.enabling.database.rmi.DatabaseException;

import eu.dnetlib.enabling.database.utils.DatabaseUtils;

@RunWith(MockitoJUnitRunner.class)

public class SQLResultSetListenerTest {

	// Class under test

	private SQLResultSetListener listener;

	private static final int SIZE = 123;

	private static final int FROM = 10;

	private static final int TO = 12;

	private static final String DB = "THE_DB";

	private static final String SQL = "SELECT * FROM test";

	private static final String SQL_FROM_TO = SQL + " OFFSET " + (FROM - 1);

	private static final String XML = "<AAAA />";

	@Mock

	private DatabaseUtils dbUtils;

	@Mock

	private Document simpleResponseDoc;

	@Mock

	private SqlRowSet ROWSET;

	@Mock

	private SqlRowSetMetaData ROWMETADATA;

	@Before

	public void setUp() throws Exception {

		listener = new SQLResultSetListener();

		listener.setDb(DB);

		listener.setDbUtils(dbUtils);

		listener.setSql(SQL);

		when(dbUtils.executeSql(eq(DB), anyString(), eq(Integer.class))).thenReturn(SIZE);

		when(dbUtils.executeSql(DB, SQL, SqlRowSet.class)).thenReturn(ROWSET);

		when(dbUtils.executeSql(DB, SQL_FROM_TO, SqlRowSet.class)).thenReturn(ROWSET);

		when(ROWSET.next()).thenReturn(true);

		when(ROWSET.getMetaData()).thenReturn(ROWMETADATA);

		when(ROWMETADATA.getColumnNames()).thenReturn(new String[] { "col1,col2,col3" });

		when(ROWSET.getObject(anyString())).thenReturn("val_XXX");

		when(dbUtils.rowToDocument(anyMap())).thenReturn(simpleResponseDoc);

		when(simpleResponseDoc.asXML()).thenReturn(XML);

	}

	@Test

	public void testGetResult_1() throws DatabaseException {

		listener.getResult(1, FROM - 1);

		listener.getResult(FROM, TO);

		verify(dbUtils).executeSql(DB, SQL, SqlRowSet.class);

		verify(ROWSET, times(TO + 2)).next();

		verify(ROWSET, times(1)).previous();

	}

	@Test

	public void testGetResult_3() throws DatabaseException {

		List<String> response = listener.getResult(FROM, TO);

		verify(dbUtils).executeSql(DB, SQL_FROM_TO, SqlRowSet.class);

		assertEquals(3, response.size());

		for (String s : response) {

			assertEquals(XML, s);

		}

	}

	@Test(expected = IllegalArgumentException.class)

	public void testGetResult_err1() {

		listener.getResult(0, 0);

	}

	@Test(expected = IllegalArgumentException.class)

	public void testGetResult_err2() {

		listener.getResult(-4, 2);

	}

	@Test(expected = IllegalArgumentException.class)

	public void testGetResult_err3() {

		listener.getResult(1, -1);

	}

	@Test(expected = IllegalArgumentException.class)

	public void testGetResult_err4() {

		listener.getResult(10, 4);

	}

	@Test

	public void testGetSize() {

		assertEquals(SIZE, listener.getSize());

	}

}

package eu.dnetlib.enabling.database.resultset;

import static org.junit.Assert.assertEquals;

import static org.junit.Assert.assertNotNull;

import static org.mockito.Matchers.anyMap;

import static org.mockito.Matchers.eq;

import static org.mockito.Mockito.when;

import java.util.Date;

import java.util.Map;

import java.util.concurrent.BlockingQueue;

import org.junit.Before;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.mockito.Mock;

import org.mockito.runners.MockitoJUnitRunner;

import eu.dnetlib.enabling.database.rmi.DatabaseException;

import eu.dnetlib.enabling.database.utils.DatabaseUtils;

import eu.dnetlib.enabling.resultset.ResultSetListener;

@RunWith(MockitoJUnitRunner.class)

public class SQLResultSetListenerFactoryTest {

	// Class under test.

	private SQLResultSetListenerFactory factory;

	private static final String DB = "THE_DB";

	private static final String TABLE = "THE_TABLE";

	@Mock

	private DatabaseUtils dbUtils;

	@Mock

	private BlockingQueue<Map<String, Object>> queue;

	@Mock

	private Date fromDATE;

	@Mock

	private Date untilDATE;

	@Before

	public void setUp() throws Exception {

		factory = new SQLResultSetListenerFactory();

		factory.setDbUtils(dbUtils);

	}

	@Test

	public void testCreateSQLListener() throws DatabaseException {

		String query = "SELECT * FROM test ";

		String countquery = "SELECT 100";

		when(dbUtils.executeSql(DB, countquery, Integer.class)).thenReturn(100);

		when(dbUtils.executeSql(DB, query, BlockingQueue.class)).thenReturn(queue);

		ResultSetListener listener = factory.createSQLListenerWithSize(DB, query, countquery);

		assertNotNull(listener);

	}

	@Test

	public void testCreateTableListener() {

		SQLResultSetListener listener = (SQLResultSetListener) factory.createTableListener(DB, TABLE);

		assertEquals(listener.getSql(), "SELECT * FROM " + TABLE);

		assertEquals(dbUtils, listener.getDbUtils());

		assertEquals(DB, listener.getDb());

	}

	@Test

	public void testCreateCondTableListener() {

		String cond = "title='XYZ'";

		SQLResultSetListener listener = (SQLResultSetListener) factory.createCondTableListener(DB, TABLE, cond);

		assertEquals(listener.getSql(), "SELECT * FROM " + TABLE + " WHERE " + cond);

		assertEquals(dbUtils, listener.getDbUtils());

		assertEquals(DB, listener.getDb());

	}

	@SuppressWarnings("unchecked")

	@Test

	public void testCreateLoggedTableListener() {

		String sql = "SELECT * FROM ...";

		when(dbUtils.getSQLFromTemplate(eq("loggedTable"), eq(DB), eq(TABLE), anyMap())).thenReturn(sql);

		SQLResultSetListener listener = (SQLResultSetListener) factory.createLoggedTableListener(DB, TABLE, fromDATE, untilDATE);

		assertEquals(listener.getSql(), sql);

		assertEquals(dbUtils, listener.getDbUtils());

		assertEquals(DB, listener.getDb());

	}

}

package eu.dnetlib.enabling.database;

import static org.junit.Assert.assertEquals;

import static org.junit.Assert.assertFalse;

import static org.junit.Assert.assertTrue;

import static org.mockito.Mockito.verify;

import static org.mockito.Mockito.when;

import java.util.Arrays;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import org.junit.Before;

import org.junit.Ignore;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.mockito.Mock;

import org.mockito.runners.MockitoJUnitRunner;

import eu.dnetlib.enabling.database.objects.DnetDatabase;

import eu.dnetlib.enabling.database.resultset.SQLResultSetListenerFactory;

import eu.dnetlib.enabling.database.utils.DatabaseUtils;

import eu.dnetlib.enabling.database.utils.GenericRow;

import eu.dnetlib.enabling.resultset.ResultSetFactory;

import eu.dnetlib.enabling.resultset.ResultSetListener;

@RunWith(MockitoJUnitRunner.class)

public class DatabaseServiceCoreTest {

	private static final String DB_NAME = "cinema";

	private static final String TABLE_NAME = "films";

	private static final String TABLE_WHERE = "year=1980";

	private static final Date TABLE_FROM = new Date(100000);

	private static final Date TABLE_UNTIL = new Date(200000);

	private static final String SQL = "SELECT * from films";

	// Class under test

	private DatabaseServiceCore core;

	@Mock

	private DatabaseUtils dbUtils;

	@Mock

	private ResultSetFactory resultSetFactory;

	@Mock

	private SQLResultSetListenerFactory resultSetListenerFactory;

	@Mock

	private ResultSetListener listener_where;

	@Mock

	private ResultSetListener listener_from_until;

	@Mock

	private ResultSetListener listener_sql;

	@Mock

	private GenericRow ROW;

	private final Map<String, Object> ROW_FIELDS = new HashMap<String, Object>();

	@Before

	public void setUp() throws Exception {

		core = new DatabaseServiceCore();

		core.setDbUtils(dbUtils);

		core.setResultSetFactory(resultSetFactory);

		core.setResultSetListenerFactory(resultSetListenerFactory);

		ROW_FIELDS.put("name", "Pippo");

		ROW_FIELDS.put("age", 36);

		when(dbUtils.listCommonDBTables(DB_NAME)).thenReturn(Arrays.asList("T1", "T2", "T3"));;

		when(resultSetListenerFactory.createCondTableListener(DB_NAME, TABLE_NAME, TABLE_WHERE)).thenReturn(listener_where);

		when(resultSetListenerFactory.createLoggedTableListener(DB_NAME, TABLE_NAME, TABLE_FROM, TABLE_UNTIL)).thenReturn(listener_from_until);

		when(resultSetListenerFactory.createSQLListener(DB_NAME, SQL)).thenReturn(listener_sql);

		//when(ROW.getTable()).thenReturn(TABLE_NAME);

		//when(ROW.getFields()).thenReturn(ROW_FIELDS);

	}

	@Ignore

	@Test

	public void testListDatabases() throws Exception {

		List<DnetDatabase> list = core.listDatabases();

		verify(dbUtils).listAllDatabases();

		assertEquals("List size invalid", 5, list.size());

		for (DnetDatabase db : list) {

			if (db.getDbName().startsWith("M")) {

				assertTrue("Db should be managed", db.isManaged());

			} else {

				assertFalse("Db should be not managed", db.isManaged());

			}

		}

	}

	@Test

	public void testChangeDatabaseStatus_manage() throws Exception {

		core.changeDatabaseStatus(DB_NAME, true);

		verify(dbUtils).listCommonDBTables(DB_NAME);

		verify(dbUtils).setManaged(DB_NAME, true);

	}

	@Test

	public void testChangeDatabaseStatus_unmanage() throws Exception {

		core.changeDatabaseStatus(DB_NAME, false);

		verify(dbUtils).listCommonDBTables(DB_NAME);

		verify(dbUtils).setManaged(DB_NAME, false);

	}

	@Test

	public void testGenerateResultSet_1() throws Exception {

		core.generateResultSet(DB_NAME, SQL);

		verify(resultSetFactory).createResultSet(listener_sql);

	}

	@Test

	public void testGenerateResultSet_2() throws Exception {

		core.generateResultSet(DB_NAME, TABLE_NAME, TABLE_WHERE);

		verify(resultSetFactory).createResultSet(listener_where);

	}

	@Test

	public void testGenerateResultSet_3() throws Exception {

		core.generateResultSet(DB_NAME, TABLE_NAME, TABLE_FROM, TABLE_UNTIL);

		verify(resultSetFactory).createResultSet(listener_from_until);

	}

}

package eu.dnetlib.enabling.database.utils;

import java.util.Map;

public class GenericRow {

	private String table;

	private Map<String, Object> fields;

	private boolean toDelete = false;

	public GenericRow(String table, Map<String, Object> fields, boolean toDelete) {

		super();

		this.table = table;

		this.fields = fields;

		this.toDelete = toDelete;

	}

	public String getTable() {

		return table;

	}

	public void setTable(String table) {

		this.table = table;

	}

	public Map<String, Object> getFields() {

		return fields;

	}

	public void setFields(Map<String, Object> fields) {

		this.fields = fields;

	}

	public boolean isToDelete() {

		return toDelete;

	}

	public void setToDelete(boolean toDelete) {

		this.toDelete = toDelete;

	}

}

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

	private int fetchSize = 100;

	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 = getJdbcTemplate(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 {

		try {

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

			for (Object obj : getJdbcTemplate(dbName).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 = getJdbcTemplate(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 {

		if (clazz == BlockingQueue.class) {

			log.debug("Creating Queue");

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

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

				final DataSource ds = dataSourceFactory.createDataSource(dbName);

				try (final Connection con = getConnection(ds);

						final PreparedStatement stm = getStm(query, con);

						final ResultSet rs = stm.executeQuery()) {

					rs.setFetchSize(getFetchSize());

					boolean timeout = false;

					log.info(String.format("[Thread Id %s] starting to populate queue", Thread.currentThread().getId()));

					while (rs.next()) {

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

						}

						if (!enqueue(queue, row)) {

							timeout = true;

							break;

						}

					}

					if (timeout) {

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

						return;

					}

					// An empty Map indicates the end of the resultset

					enqueue(queue, new HashMap<>());

				} catch (SQLException e) {

					throw new RuntimeException(e);

				}

			});

			log.debug("Returned Queue");

			return (T) queue;

		}

		final JdbcTemplate jdbcTemplate = jdbcTemplateFactory.createJdbcTemplate(dbName);

		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 {

			jdbcTemplate.update(query);

			return null;

		}

	}

	private boolean enqueue(final ArrayBlockingQueue<Map<String, Object>> q, final Map<String, Object> row) {

		try {

			return q.offer(row, BLOCKING_QUEUE_TIMEOUT, TimeUnit.SECONDS);

		} catch (InterruptedException e) {

			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;

	}

	private Connection getConnection(final DataSource dataSource) throws SQLException {

		final Connection conn = dataSource.getConnection();

		conn.setAutoCommit(false);

		return conn;

	}

	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 {

			final JdbcTemplate jdbcTemplate = getJdbcTemplate(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 {

		final JdbcTemplate jdbcTemplate = getJdbcTemplate(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 = getJdbcTemplate(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 = getJdbcTemplate(db);

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

		final JdbcTemplate jdbcTemplate = getJdbcTemplate(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);

		final JdbcTemplate jdbcTemplate = getJdbcTemplate(database);

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

	}

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

		verifyParameters(database, table);

		final JdbcTemplate jdbcTemplate = getJdbcTemplate(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);

		final JdbcTemplate jdbcTemplate = getJdbcTemplate(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);

		final JdbcTemplate jdbcTemplate = getJdbcTemplate(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);

		final JdbcTemplate jdbcTemplate = getJdbcTemplate(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);

		final JdbcTemplate jdbcTemplate = getJdbcTemplate(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>();