2 files changed, 201 insertions, 0 deletions
diff --git a/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/util/Helper.java b/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/util/Helper.java
index b808206..618b06f 100644
--- a/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/util/Helper.java
+++ b/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/util/Helper.java
@@ -6,6 +6,12 @@ import java.util.List;
 import javafx.stage.Modality;
 import javafx.stage.Stage;
 import javafx.stage.Window;
+import org.dbunit.database.IDatabaseConnection;
+import org.dbunit.dataset.DataSetException;
+import org.dbunit.dataset.IDataSet;
+import org.dbunit.dataset.ITable;
+import org.dbunit.dataset.filter.DefaultColumnFilter;
+import org.dbunit.util.fileloader.FlatXmlDataFileLoader;
 import org.testfx.api.FxRobotContext;
 
 public class Helper {
@@ -25,4 +31,39 @@ public class Helper {
                         .findFirst()
                         .orElse(null);
     }
+
+    public static ITable getActualFilteredTableData(
+            IDatabaseConnection connection, String tableName, String[] excludedColumns)
+            throws Exception {
+        IDataSet actualDataSet = connection.createDataSet();
+        ITable actualTable = actualDataSet.getTable(tableName);
+        return getFilteredTableData(actualTable, excludedColumns);
+    }
+
+    public static ITable getExpectedFilteredTableData(
+            String tableName, String[] excludedColumns, String expectedXmlDataFileName)
+            throws Exception {
+        IDataSet dataSet = loadDataSet(expectedXmlDataFileName);
+        ITable table = dataSet.getTable(tableName);
+        return getFilteredTableData(table, excludedColumns);
+    }
+
+    public static ITable getActualFilteredQueryTableData(
+            IDatabaseConnection connection,
+            String resultTableName,
+            String sql,
+            String[] excludedColumns)
+            throws Exception {
+        ITable queryData = connection.createQueryTable(resultTableName, sql);
+        return getFilteredTableData(queryData, excludedColumns);
+    }
+
+    private static ITable getFilteredTableData(ITable tableData, String[] excludedColumns)
+            throws DataSetException {
+        return DefaultColumnFilter.excludedColumnsTable(tableData, excludedColumns);
+    }
+
+    private static IDataSet loadDataSet(String fileName) {
+        return new FlatXmlDataFileLoader().load("/" + fileName);
+    }
 }
diff --git a/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/util/JdbcTestCase.java b/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/util/JdbcTestCase.java
new file mode 100644
index 0000000..c509a1f
--- /dev/null
+++ b/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/util/JdbcTestCase.java
@@ -0,0 +1,160 @@
+package at.ac.tuwien.sepm.assignment.groupphase.util;
+
+import static org.dbunit.Assertion.assertEquals;
+
+import java.io.InputStream;
+import java.lang.invoke.MethodHandles;
+import java.sql.SQLException;
+import java.sql.Types;
+import java.util.Collection;
+import java.util.Collections;
+import org.dbunit.DefaultDatabaseTester;
+import org.dbunit.IDatabaseTester;
+import org.dbunit.IOperationListener;
+import org.dbunit.database.DatabaseConfig;
+import org.dbunit.database.DatabaseConnection;
+import org.dbunit.database.IDatabaseConnection;
+import org.dbunit.dataset.DataSetException;
+import org.dbunit.dataset.IDataSet;
+import org.dbunit.dataset.datatype.DataType;
+import org.dbunit.dataset.datatype.DataTypeException;
+import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
+import org.dbunit.ext.postgresql.PostgresqlDataTypeFactory;
+import org.dbunit.operation.DatabaseOperation;
+import org.junit.After;
+import org.junit.Before;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import org.springframework.stereotype.Component;
+
+@Component
+public abstract class JdbcTestCase {
+
+    private static final Logger LOG = LoggerFactory.getLogger(MethodHandles.lookup().lookupClass());
+    private static final String JDBC_URL =
+            "jdbc:h2:mem:test;INIT=RUNSCRIPT FROM 'classpath:sql/database.sql'";
+
+    private IDatabaseTester dbTester;
+    private IDatabaseConnection connection;
+    private IOperationListener operationListener;
+    private JDBCConnectionManager jdbcConnectionManager;
+
+    protected JdbcTestCase() {
+        jdbcConnectionManager = new JDBCConnectionManager(JDBC_URL);
+    }
+
+    protected abstract IDataSet getDataSet() throws Exception;
+
+    protected IDataSet getDataSet(String xmlname) throws DataSetException {
+        InputStream res = getClass().getClassLoader().getResourceAsStream(xmlname);
+        FlatXmlDataSetBuilder builder = new FlatXmlDataSetBuilder();
+        builder.setColumnSensing(true);
+        return builder.build(res);
+    }
+
+    protected JDBCConnectionManager getJdbcConnectionManager() {
+        return jdbcConnectionManager;
+    }
+
+    protected IDatabaseConnection getConnection() throws Exception {
+        if (connection == null)
+            connection = new DatabaseConnection(jdbcConnectionManager.getConnection(), null, true);
+
+        return connection;
+    }
+
+    private IOperationListener getOperationListener() {
+        if (operationListener == null) {
+            operationListener =
+                    new IOperationListener() {
+                        @Override
+                        public void connectionRetrieved(IDatabaseConnection connection) {
+                            setUpDatabaseConfig(connection.getConfig());
+                        }
+
+                        @Override
+                        public void operationSetUpFinished(IDatabaseConnection connection) {
+                            LOG.debug("operationSetUpFinished(connection={}) - start", connection);
+                        }
+
+                        @Override
+                        public void operationTearDownFinished(IDatabaseConnection connection) {
+                            LOG.debug(
+                                    "operationTearDownFinished(connection={}) - start", connection);
+                            try {
+                                connection.close();
+                            } catch (SQLException e) {
+                                LOG.error("Failed to close connection:" + e);
+                                e.printStackTrace();
+                            }
+                        }
+                    };
+        }
+
+        return operationListener;
+    }
+
+    // override DBUnit's enum handling
+    private void setUpDatabaseConfig(DatabaseConfig config) {
+        PostgresqlDataTypeFactory factory =
+                new PostgresqlDataTypeFactory() {
+                    @Override
+                    public boolean isEnumType(String sqlTypeName) {
+                        if (sqlTypeName.equalsIgnoreCase("enum")) return true;
+
+                        return super.isEnumType(sqlTypeName);
+                    }
+
+                    @Override
+                    public DataType createDataType(int sqlType, String sqlTypeName)
+                            throws DataTypeException {
+                        if (isEnumType(sqlTypeName)) {
+                            sqlType = Types.VARCHAR;
+                        }
+
+                        return super.createDataType(sqlType, sqlTypeName);
+                    }
+
+                    @Override
+                    public Collection getValidDbProducts() {
+                        return Collections.singletonList("H2");
+                    }
+                };
+
+        config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, factory);
+    }
+
+    protected void compareWith(String xmlname, String[] tables) throws Exception {
+        InputStream res = getClass().getClassLoader().getResourceAsStream(xmlname);
+        IDataSet actual = getConnection().createDataSet();
+        IDataSet expected = new FlatXmlDataSetBuilder().build(res);
+
+        for (String table : tables) {
+            assertEquals(actual.getTable(table), expected.getTable(table));
+        }
+    }
+
+    @Before
+    public void setUp() throws Exception {
+        IDataSet dataSet = getDataSet();
+
+        dbTester = new DefaultDatabaseTester(getConnection());
+        getOperationListener().connectionRetrieved(getConnection());
+
+        dbTester.setSetUpOperation(DatabaseOperation.CLEAN_INSERT);
+        dbTester.setTearDownOperation(DatabaseOperation.REFRESH);
+        dbTester.setDataSet(dataSet);
+        dbTester.setOperationListener(getOperationListener());
+        dbTester.onSetup();
+    }
+
+    @After
+    public void tearDown() throws Exception {
+        try {
+            dbTester.onTearDown();
+            getConnection().close();
+        } finally {
+            dbTester = null;
+        }
+    }
+}