D-Net

{"type_source": "SVN", "goal": "package -U -T 4C source:jar", "url": "http://svn-public.driver.research-infrastructures.eu/driver/dnet40/modules/cnr-enabling-database-service/trunk/", "deploy_repository": "dnet4-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/dnet4-snapshots", "name": "cnr-enabling-database-service"}

package eu.dnetlib.enabling.database.utils;

import static org.junit.Assert.assertEquals;

import static org.junit.Assert.assertNotNull;

import static org.junit.Assert.assertTrue;

import static org.mockito.Matchers.any;

import static org.mockito.Matchers.anyObject;

import static org.mockito.Matchers.anyString;

import static org.mockito.Mockito.atLeastOnce;

import static org.mockito.Mockito.never;

import static org.mockito.Mockito.times;

import static org.mockito.Mockito.verify;

import static org.mockito.Mockito.when;

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

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

		GenericRow row2 = new GenericRow(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());

	}

}

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

import org.springframework.jdbc.core.JdbcTemplate;

import eu.dnetlib.enabling.database.DataSourceFactory;

@RunWith(MockitoJUnit44Runner.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.*;

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

@RunWith(MockitoJUnit44Runner.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 static org.mockito.Mockito.verify;

import java.util.Date;

import org.junit.Before;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.mockito.Mock;

import org.mockito.runners.MockitoJUnit44Runner;

@RunWith(MockitoJUnit44Runner.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() {

		impl.dumpTable(DBNAME, TABLENAME);

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

	}

	@Test

	public void testDumpTableAndLogs() {

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

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

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

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

import eu.dnetlib.enabling.resultset.ResultSetListener;

@RunWith(MockitoJUnit44Runner.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.MockitoJUnit44Runner;

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(MockitoJUnit44Runner.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.Array;

import java.sql.Date;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.text.ParseException;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.concurrent.BlockingQueue;

import java.util.concurrent.Executors;

import java.util.concurrent.LinkedBlockingQueue;

import java.util.concurrent.TimeUnit;

import java.util.regex.Pattern;

import javax.sql.DataSource;

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;

import com.google.common.collect.Lists;

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;

public class DatabaseUtils {

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

	private DataSourceFactory dataSourceFactory;

	private JdbcTemplateFactory jdbcTemplateFactory;

	private TransactionTemplateFactory transactionTemplateFactory;

	private String defaultDB;

	private VelocityEngine velocityEngine;

	private String dbPrefix;

	private int numbersOfRecordsForTransaction;

	public static final String DNET_RESOURCE_ID_FIELD = "_dnet_resource_identifier_";

	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;

	public static final int BLOCKING_QUEUE_TIMEOUT = 300;

	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;

		}

	}

	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.info("Creating Queue");

				final LinkedBlockingQueue<Map<String, Object>> q = new LinkedBlockingQueue<Map<String, Object>>(BLOCKING_QUEUE_SIZE);

				Runnable run = new Runnable() {

					@Override

					public void run() {

						try {

							jdbcTemplate.query(query, 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 (!q.offer(row, BLOCKING_QUEUE_TIMEOUT, TimeUnit.SECONDS)) {

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

											rs.close();

											Thread.currentThread().interrupt();

											return;

										}

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

									} catch (InterruptedException e) {

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

									}

								}

							});

						} catch (Throwable e) {

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

						}

						try {

							// An empty Map indicates the end of the resultset

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

						} catch (InterruptedException e) {

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

						}

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

					}

				};

				Executors.newSingleThreadExecutor().submit(run);

				log.info("Returned Queue");

				return (T) q;

			} else {

				jdbcTemplate.update(query);

				return null;

			}

		} catch (DataAccessException e) {

			throw new DatabaseException(e);

		}

	}

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

		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 : false;

		} catch (DatabaseException e) {

			throw new RuntimeException("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, new Object[] { 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 counterDone = 0;

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

				counterDone += importTransaction(jdbcTemplate, transactionTemplate, rows);

				rows.clear();

			}

		}

		counterTotal += rows.size();

		counterDone += importTransaction(jdbcTemplate, transactionTemplate, rows);

		long end = DateUtils.now();

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

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

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

	}

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

		if (rows == null) return 0;

		int res = rows.size();

		while (!rows.isEmpty()) {

			List<GenericRow> ok = importTransactionInternal(jdbcTemplate, transactionTemplate, rows);

			if (ok.size() < rows.size()) {

				importTransactionInternal(jdbcTemplate, transactionTemplate, ok);

				res--;

				if ((ok.size() + 1) < rows.size()) {

					rows = rows.subList(ok.size() + 1, rows.size());

				} else {

					rows.clear();

				}

			} else {

				rows.clear();

			}

		}

		return res;

	}

	private List<GenericRow> importTransactionInternal(final JdbcTemplate jdbcTemplate,

			final TransactionTemplate transactionTemplate,

			final List<GenericRow> rows) {

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

				}

				return ok;

			}

		});

	}

	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 + "=?", new Object[] { 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,