Fix DBUnit's enums and revert database changes

3 files changed, 93 insertions, 128 deletions

diff --git a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/VehicleDatabaseDao.java b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/VehicleDatabaseDao.java
index 8f0d28b..3e8c0fc 100644
--- a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/VehicleDatabaseDao.java
+++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/VehicleDatabaseDao.java
@@ -162,9 +162,9 @@ public class VehicleDatabaseDao implements VehicleDAO {
         return Vehicle.builder()
                 .id(rs.getLong("id"))
                 .name(rs.getString("name"))
-                .constructionType(ConstructionType.valueOf(rs.getString("constructionType")))
+                .constructionType(ConstructionType.values()[rs.getInt("constructionType")])
                 .type(VehicleType.valueOf(rs.getString("type")))
-                .status(Status.valueOf(rs.getString("constructionType")))
+                .status(Status.values()[rs.getInt("status")])
                 .hasNef(rs.getBoolean("hasNef"))
                 .build();
     }
diff --git a/src/main/resources/sql/database.sql b/src/main/resources/sql/database.sql
index 4f3adf7..ddedeac 100644
--- a/src/main/resources/sql/database.sql
+++ b/src/main/resources/sql/database.sql
@@ -1,30 +1,26 @@
 CREATE TABLE IF NOT EXISTS VehicleVersion (
   id BIGINT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(100) NOT NULL,
-  constructionType VARCHAR NOT NULL,
-  type VARCHAR NOT NULL,
+  constructionType ENUM('NORMAL', 'HOCHDACH', 'MITTELHOCHDACH') NOT NULL,
+  type ENUM('BKTW', 'KTW_B', 'KTW', 'RTW', 'NEF', 'NAH') NOT NULL,
   hasNef BOOLEAN NOT NULL,
-  CHECK constructionType IN ('NORMAL', 'HOCHDACH', 'MITTELHOCHDACH'),
-  CHECK type IN ('BKTW', 'KTW-B', 'KTW', 'RTW', 'NEF', 'NAH')
 );
 
 CREATE TABLE IF NOT EXISTS Vehicle (
   id BIGINT AUTO_INCREMENT PRIMARY KEY,
   version BIGINT NOT NULL,
-  status VARCHAR NOT NULL,
+  status ENUM('ABGEMELDET', 'FREI_WACHE', 'ZUM_BERUFUNGSORT', 'AM_BERUFUNGSORT', 'ZUM_ZIELORT',
+              'AM_ZIELORT', 'FREI_FUNK', 'DELETED') NOT NULL,
   FOREIGN KEY (version) REFERENCES VehicleVersion(id),
-  CHECK status IN ('ABGEMELDET', 'FREI_WACHE', 'ZUM_BERUFUNGSORT', 'AM_BERUFUNGSORT', 'ZUM_ZIELORT',
-              'AM_ZIELORT', 'FREI_FUNK', 'DELETED')
 );
 
 CREATE TABLE IF NOT EXISTS EmployeeVersion (
   id BIGINT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(100) NOT NULL,
   birthday DATE NOT NULL,
-  educationLevel VARCHAR NOT NULL,
+  educationLevel ENUM('RS', 'NFS', 'NKV', 'NKA', 'NKI', 'NA') NOT NULL,
   isDriver BOOLEAN NOT NULL,
   isPilot BOOLEAN NOT NULL,
-  CHECK educationLevel IN ('RS', 'NFS', 'NKV', 'NKA', 'NKI', 'NA')
 );
 
 CREATE TABLE IF NOT EXISTS Employee (
@@ -47,13 +43,11 @@ CREATE TABLE IF NOT EXISTS Registration (
 CREATE TABLE IF NOT EXISTS Operation (
   id BIGINT AUTO_INCREMENT PRIMARY KEY,
   opCode VARCHAR(20) NOT NULL,
-  severity VARCHAR NOT NULL,
+  severity ENUM('A', 'B', 'C', 'D', 'E', 'O') NOT NULL,
   created TIMESTAMP NOT NULL,
   destination VARCHAR(100) NOT NULL,
   additionalInfo VARCHAR(100),
-  status VARCHAR NOT NULL,
-  CHECK severity IN ('A', 'B', 'C', 'D', 'E', 'O'),
-  CHECK status IN ('ACTIVE', 'COMPLETED', 'CANCELLED')
+  status ENUM('ACTIVE', 'COMPLETED', 'CANCELLED'),
 );
 
 CREATE TABLE IF NOT EXISTS VehicleOperation (
diff --git a/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/employee/EmployeePersistenceTest.java b/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/employee/EmployeePersistenceTest.java
index f8fe0f3..e8d0d4d 100644
--- a/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/employee/EmployeePersistenceTest.java
+++ b/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/employee/EmployeePersistenceTest.java
@@ -1,155 +1,126 @@
 package at.ac.tuwien.sepm.assignment.groupphase.employee;
 
-import static junit.framework.TestCase.fail;
-
 import at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dao.EmployeeDAO;
 import at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dao.EmployeeDatabaseDao;
 import at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dto.Employee;
 import at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dto.Employee.EducationLevel;
 import at.ac.tuwien.sepm.assignment.groupphase.exception.PersistenceException;
 import at.ac.tuwien.sepm.assignment.groupphase.util.JDBCConnectionManager;
-import java.nio.charset.Charset;
-import java.sql.SQLException;
+import java.io.InputStream;
+import java.sql.Types;
 import java.time.LocalDate;
-import java.time.format.DateTimeFormatter;
 import java.util.List;
-import org.dbunit.IDatabaseTester;
-import org.dbunit.JdbcDatabaseTester;
+import org.dbunit.DBTestCase;
+import org.dbunit.PropertiesBasedJdbcDatabaseTester;
+import org.dbunit.database.DatabaseConfig;
 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.operation.DatabaseOperation;
-import org.h2.tools.RunScript;
+import org.dbunit.ext.postgresql.PostgresqlDataTypeFactory;
 import org.junit.Assert;
-import org.junit.Before;
-import org.junit.BeforeClass;
-import org.junit.Test;
 
-public class EmployeePersistenceTest {
+public class EmployeePersistenceTest extends DBTestCase {
 
     private static final String JDBC_DRIVER = org.h2.Driver.class.getName();
-    private static final String JDBC_URL = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1";
-    private static final String USER = "";
-    private static final String PASSWORD = "";
+    private static final String JDBC_URL =
+            "jdbc:h2:mem:test;INIT=RUNSCRIPT FROM 'classpath:sql/database.sql'";
 
     private EmployeeDAO employeePersistence;
 
     public EmployeePersistenceTest() throws PersistenceException {
         employeePersistence = new EmployeeDatabaseDao(new JDBCConnectionManager(JDBC_URL));
-    }
 
-    @BeforeClass
-    public static void createSchema() throws SQLException {
-        RunScript.execute(
-                JDBC_URL,
-                USER,
-                PASSWORD,
-                "classpath:sql/database.sql",
-                Charset.forName("UTF8"),
-                false);
+        System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_DRIVER_CLASS, JDBC_DRIVER);
+        System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_CONNECTION_URL, JDBC_URL);
     }
 
-    @Before
-    public void importDataSet() throws Exception {
-        IDataSet dataSet = readDataSet();
-        cleanlyInsert(dataSet);
+    @Override
+    protected IDataSet getDataSet() throws DataSetException {
+        InputStream res =
+                getClass().getClassLoader().getResourceAsStream("employeeServiceTestData.xml");
+        return new FlatXmlDataSetBuilder().build(res);
     }
 
-    private IDataSet readDataSet() throws DataSetException {
-        return new FlatXmlDataSetBuilder()
-                .build(
-                        getClass()
-                                .getClassLoader()
-                                .getResourceAsStream("employeeServiceTestData.xml"));
-    }
+    @Override
+    protected void setUpDatabaseConfig(DatabaseConfig config) {
+        PostgresqlDataTypeFactory factory =
+                new PostgresqlDataTypeFactory() {
+                    @Override
+                    public boolean isEnumType(String sqlTypeName) {
+                        if (sqlTypeName.equalsIgnoreCase("enum")) return true;
 
-    private void cleanlyInsert(IDataSet dataSet) throws Exception {
-        IDatabaseTester databaseTester =
-                new JdbcDatabaseTester(JDBC_DRIVER, JDBC_URL, USER, PASSWORD);
+                        return super.isEnumType(sqlTypeName);
+                    }
 
-        databaseTester.setSetUpOperation(DatabaseOperation.CLEAN_INSERT);
-        databaseTester.setDataSet(dataSet);
-        databaseTester.onSetup();
-    }
+                    @Override
+                    public DataType createDataType(int sqlType, String sqlTypeName)
+                            throws DataTypeException {
+                        if (isEnumType(sqlTypeName)) {
+                            sqlType = Types.VARCHAR;
+                        }
 
-    @Test
-    public void testListEmployees() {
+                        return super.createDataType(sqlType, sqlTypeName);
+                    }
+                };
 
-        try {
-            List<Employee> employees = employeePersistence.list();
+        config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, factory);
+    }
 
-            Employee empOne =
-                    Employee.builder()
-                            .id(1)
-                            .name("Adam")
-                            .birthday(
-                                    LocalDate.parse(
-                                            "10.10.2010",
-                                            DateTimeFormatter.ofPattern("dd.MM.yyyy")))
-                            .educationLevel(EducationLevel.RS)
-                            .isDriver(true)
-                            .isPilot(false)
-                            .build();
+    public void testListEmployees() throws PersistenceException {
+        List<Employee> employees = employeePersistence.list();
 
-            Employee empTwo =
-                    Employee.builder()
-                            .id(2)
-                            .name("Max")
-                            .birthday(
-                                    LocalDate.parse(
-                                            "11.11.1990",
-                                            DateTimeFormatter.ofPattern("dd.MM.yyyy")))
-                            .educationLevel(EducationLevel.NFS)
-                            .isDriver(false)
-                            .isPilot(false)
-                            .build();
+        System.out.println(LocalDate.parse("2010-10-10"));
+        Employee empOne =
+                Employee.builder()
+                        .id(1)
+                        .name("Adam")
+                        .birthday(LocalDate.parse("2010-10-10"))
+                        .educationLevel(EducationLevel.RS)
+                        .isDriver(true)
+                        .isPilot(false)
+                        .build();
 
-            Employee empThree =
-                    Employee.builder()
-                            .id(3)
-                            .name("Lisa")
-                            .birthday(
-                                    LocalDate.parse(
-                                            "16.10.1999",
-                                            DateTimeFormatter.ofPattern("dd.MM.yyyy")))
-                            .educationLevel(EducationLevel.NKI)
-                            .isDriver(true)
-                            .isPilot(false)
-                            .build();
+        Employee empTwo =
+                Employee.builder()
+                        .id(2)
+                        .name("Max")
+                        .birthday(LocalDate.parse("1990-11-11"))
+                        .educationLevel(EducationLevel.NFS)
+                        .isDriver(false)
+                        .isPilot(false)
+                        .build();
 
-            Assert.assertTrue(employees.contains(empOne));
-            Assert.assertTrue(employees.contains(empTwo));
-            Assert.assertTrue(employees.contains(empThree));
-            Assert.assertEquals(3, employees.size());
+        Employee empThree =
+                Employee.builder()
+                        .id(3)
+                        .name("Lisa")
+                        .birthday(LocalDate.parse("1999-10-16"))
+                        .educationLevel(EducationLevel.NKI)
+                        .isDriver(true)
+                        .isPilot(false)
+                        .build();
 
-        } catch (PersistenceException e) {
-            fail();
-        }
+        Assert.assertTrue(employees.contains(empOne));
+        Assert.assertTrue(employees.contains(empTwo));
+        Assert.assertTrue(employees.contains(empThree));
+        Assert.assertEquals(3, employees.size());
     }
 
-    @Test
-    public void testEmployeeListNoElement() {
-
-        try {
-            List<Employee> employees = employeePersistence.list();
-
-            Employee empOne =
-                    Employee.builder()
-                            .id(10)
-                            .name("Adam")
-                            .birthday(
-                                    LocalDate.parse(
-                                            "10.10.2010",
-                                            DateTimeFormatter.ofPattern("dd.MM.yyyy")))
-                            .educationLevel(EducationLevel.RS)
-                            .isDriver(true)
-                            .isPilot(false)
-                            .build();
+    public void testEmployeeListNoElement() throws PersistenceException {
+        List<Employee> employees = employeePersistence.list();
 
-            Assert.assertFalse(employees.contains(empOne));
+        Employee empOne =
+                Employee.builder()
+                        .id(10)
+                        .name("Adam")
+                        .birthday(LocalDate.parse("2010-10-10"))
+                        .educationLevel(EducationLevel.RS)
+                        .isDriver(true)
+                        .isPilot(false)
+                        .build();
 
-        } catch (PersistenceException e) {
-            fail();
-        }
+        Assert.assertFalse(employees.contains(empOne));
     }
 }