D-Net

package eu.dnetlib.enabling.database.utils;

import java.io.StringReader;

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

import org.springframework.transaction.support.TransactionTemplate;

import static org.junit.Assert.*;

import static org.mockito.Matchers.any;

import static org.mockito.Matchers.anyObject;

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;

	@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(jdbcTemplateFactory.createJdbcTemplate(dataSource)).thenReturn(jdbcTemplate);

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

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

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

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

	}

	@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("1", TABLE, MAP, true);

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

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

		dbUtils.importFromIterable(DB, iterable);

		verify(jdbcTemplateFactory).createJdbcTemplate(dataSource);

		verify(transactionTemplateFactory).createTransactionTemplate(dataSource);

		verify(transactionTemplate, times(1)).execute((TransactionCallback) anyObject());

	}

	@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[]) anyObject())).thenReturn(1);

		dbUtils.prepareManagementOfTable(DB, TABLE);

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

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

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

	}

	@Ignore

	@SuppressWarnings("unchecked")

	@Test

	public void testPrepareManagementOfTable_2() throws Exception {

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

		dbUtils.prepareManagementOfTable(DB, TABLE);

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

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

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

	}

	@Ignore

	@SuppressWarnings("unchecked")

	@Test

	public void testClearManagementOfTable() throws Exception {

		when(jdbcTemplate.queryForObject(anyString(), any(Class.class), (Object[]) anyObject())).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[]) anyObject())).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);

	}

}