From 708ed158c41ee2ee5dfce64fdecf89aed4c17685 Mon Sep 17 00:00:00 2001 From: Tharre Date: Tue, 1 May 2018 22:36:49 +0200 Subject: Add sql database schema --- src/main/resources/sql/database.sql | 57 +++++++++++++++++++++++++++++++++++++ 1 file changed, 57 insertions(+) create mode 100644 src/main/resources/sql/database.sql (limited to 'src/main/resources/sql') diff --git a/src/main/resources/sql/database.sql b/src/main/resources/sql/database.sql new file mode 100644 index 0000000..9d1b0e1 --- /dev/null +++ b/src/main/resources/sql/database.sql @@ -0,0 +1,57 @@ +CREATE TABLE IF NOT EXISTS VehicleVersion ( + id BIGINT AUTO_INCREMENT PRIMARY KEY, + name VARCHAR(100) NOT NULL, + constructionType ENUM('Normal', 'Hochdach', 'Mittelhochdach') NOT NULL, + type ENUM('BKTW', 'KTW-B', 'KTW', 'RTW', 'NEF', 'NAH') NOT NULL, +); + +CREATE TABLE IF NOT EXISTS Vehicle ( + id BIGINT AUTO_INCREMENT PRIMARY KEY, + version BIGINT 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), +); + +CREATE TABLE IF NOT EXISTS EmployeeVersion ( + id BIGINT AUTO_INCREMENT PRIMARY KEY, + name VARCHAR(100) NOT NULL, + birthday DATE NOT NULL, + educationLevel ENUM('RS', 'NFS', 'NKV', 'NKA', 'NKI', 'NA') NOT NULL, + isDriver BOOLEAN NOT NULL, + isPilot BOOLEAN NOT NULL, +); + +CREATE TABLE IF NOT EXISTS Employee ( + id BIGINT AUTO_INCREMENT PRIMARY KEY, + version BIGINT NOT NULL, + FOREIGN KEY (version) REFERENCES EmployeeVersion(id), +); + +CREATE TABLE IF NOT EXISTS Registration ( + id BIGINT AUTO_INCREMENT PRIMARY KEY, + vehicleId BIGINT NOT NULL, + employeeId BIGINT NOT NULL, + start TIMESTAMP NOT NULL, + end TIMESTAMP NOT NULL, + active BOOLEAN NOT NULL, + FOREIGN KEY (vehicleId) REFERENCES VehicleVersion(id), + FOREIGN KEY (employeeId) REFERENCES EmployeeVersion(id), +); + +CREATE TABLE IF NOT EXISTS Operation ( + id BIGINT AUTO_INCREMENT PRIMARY KEY, + opCode VARCHAR(20) NOT NULL, + severity ENUM('A', 'B', 'C', 'D', 'E', 'O') NOT NULL, + created TIMESTAMP NOT NULL, + destination VARCHAR(100) NOT NULL, + additionalInfo VARCHAR(100), +); + +CREATE TABLE IF NOT EXISTS VehicleOperation ( + vehicleId BIGINT NOT NULL, + operationId BIGINT NOT NULL, + FOREIGN KEY (vehicleId) REFERENCES VehicleVersion(id), + FOREIGN KEY (operationId) REFERENCES Operation(id), + PRIMARY KEY (vehicleId, operationId), +); -- cgit v1.2.3-70-g09d2 From 7077b817bff797c769191046f0019508ddb20eba Mon Sep 17 00:00:00 2001 From: Tharre Date: Thu, 3 May 2018 23:43:27 +0200 Subject: Add missing hasNef attr. to VehicleVersion table --- src/main/resources/sql/database.sql | 1 + 1 file changed, 1 insertion(+) (limited to 'src/main/resources/sql') diff --git a/src/main/resources/sql/database.sql b/src/main/resources/sql/database.sql index 9d1b0e1..bb23c91 100644 --- a/src/main/resources/sql/database.sql +++ b/src/main/resources/sql/database.sql @@ -3,6 +3,7 @@ CREATE TABLE IF NOT EXISTS VehicleVersion ( name VARCHAR(100) NOT NULL, constructionType ENUM('Normal', 'Hochdach', 'Mittelhochdach') NOT NULL, type ENUM('BKTW', 'KTW-B', 'KTW', 'RTW', 'NEF', 'NAH') NOT NULL, + hasNef BOOLEAN NOT NULL, ); CREATE TABLE IF NOT EXISTS Vehicle ( -- cgit v1.2.3-70-g09d2 From 5a6c00ebc1583e0505fb795b3483f8937e7b8eb4 Mon Sep 17 00:00:00 2001 From: Felix Kehrer Date: Sun, 6 May 2018 16:01:10 +0200 Subject: Added groundwork for DAO tests --- pom.xml | 4 +- .../sql/H2RegistrationDAOTest_depopulate.sql | 5 ++ .../sql/H2RegistrationDAOTest_populate.sql | 10 ++++ .../dao/H2RegistrationDAOTest.java | 65 ++++++++++++++++++++++ 4 files changed, 82 insertions(+), 2 deletions(-) create mode 100644 src/main/resources/sql/H2RegistrationDAOTest_depopulate.sql create mode 100644 src/main/resources/sql/H2RegistrationDAOTest_populate.sql create mode 100644 src/test/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/H2RegistrationDAOTest.java (limited to 'src/main/resources/sql') diff --git a/pom.xml b/pom.xml index 42f24e7..95747aa 100644 --- a/pom.xml +++ b/pom.xml @@ -66,13 +66,13 @@ ${auto-value.version} provided - com.h2database h2 ${h2.version} - runtime + compile + ch.qos.logback logback-classic diff --git a/src/main/resources/sql/H2RegistrationDAOTest_depopulate.sql b/src/main/resources/sql/H2RegistrationDAOTest_depopulate.sql new file mode 100644 index 0000000..f43b641 --- /dev/null +++ b/src/main/resources/sql/H2RegistrationDAOTest_depopulate.sql @@ -0,0 +1,5 @@ +DELETE FROM Registration; +DELETE FROM Vehicle; +DELETE FROM VehicleVersion; +DELETE FROM Employee; +DELETE FROM EmployeeVersion; \ No newline at end of file diff --git a/src/main/resources/sql/H2RegistrationDAOTest_populate.sql b/src/main/resources/sql/H2RegistrationDAOTest_populate.sql new file mode 100644 index 0000000..8322479 --- /dev/null +++ b/src/main/resources/sql/H2RegistrationDAOTest_populate.sql @@ -0,0 +1,10 @@ +INSERT INTO EmployeeVersion (id, name, birthday, educationLevel, isDriver, isPilot) VALUES (1, 'John Doe', '2000-01-01', 'RS', TRUE, TRUE); +INSERT INTO EmployeeVersion (id, name, birthday, educationLevel, isDriver, isPilot) VALUES (2, 'Nick "Kage" Verily', '1990-01-01', 'NKV', TRUE, FALSE); +INSERT INTO EmployeeVersion (id, name, birthday, educationLevel, isDriver, isPilot) VALUES (3, 'Nicht Arzt', '1980-01-01', 'NA', FALSE, FALSE); +INSERT INTO Employee (id, version) VALUES (1, 1); +INSERT INTO Employee (id, version) VALUES (2, 2); +INSERT INTO Employee (id, version) VALUES (3, 3); +INSERT INTO VehicleVersion (id, name, constructionType, type) VALUES (1, 'RTW-1', 'Hochdach', 'RTW'); +INSERT INTO VehicleVersion (id, name, constructionType, type) VALUES (2, 'NEF-1', 'Normal', 'NEF'); +INSERT INTO Vehicle (id, version, status) VALUES (1, 1, 'abgemeldet'); +INSERT INTO Vehicle (id, version, status) VALUES (2, 2, 'abgemeldet'); \ No newline at end of file diff --git a/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/H2RegistrationDAOTest.java b/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/H2RegistrationDAOTest.java new file mode 100644 index 0000000..03b70b1 --- /dev/null +++ b/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/H2RegistrationDAOTest.java @@ -0,0 +1,65 @@ +package at.ac.tuwien.sepm.assignment.groupphase.einsatzverwaltung.dao; + +import static org.junit.Assert.*; + +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 org.h2.tools.RunScript; +import org.junit.After; +import org.junit.Before; +import org.junit.BeforeClass; +import org.junit.Test; + +public class H2RegistrationDAOTest { + + // Base taken from EmployeePersistenceTest + + 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 RegistrationDAO registrationDAO; + + public H2RegistrationDAOTest() throws PersistenceException { + this.registrationDAO = new H2RegistrationDAO(new JDBCConnectionManager(JDBC_URL)); + } + + @BeforeClass + public static void setupDatabase() throws SQLException { + RunScript.execute( + JDBC_URL, + USER, + PASSWORD, + "classpath:sql/database.sql", + Charset.forName("UTF8"), + false); + } + + @Before + public void setUp() throws SQLException { + RunScript.execute( + JDBC_URL, + USER, + PASSWORD, + "classpath:sql/H2RegistrationDAOTest_populate.sql", + Charset.forName("UTF8"), + false); + } + + @After + public void tearDown() throws SQLException { + RunScript.execute( + JDBC_URL, + USER, + PASSWORD, + "classpath:sql/H2RegistrationDAOTest_depopulate.sql", + Charset.forName("UTF8"), + false); + } + + @Test + public void add() {} +} -- cgit v1.2.3-70-g09d2 From 12302ff88604440cac2257741b4502b9b173d708 Mon Sep 17 00:00:00 2001 From: Felix Kehrer Date: Mon, 7 May 2018 14:53:00 +0200 Subject: Changed test data to be in sync with new database schema --- src/main/resources/sql/H2RegistrationDAOTest_populate.sql | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'src/main/resources/sql') diff --git a/src/main/resources/sql/H2RegistrationDAOTest_populate.sql b/src/main/resources/sql/H2RegistrationDAOTest_populate.sql index 8322479..b81eb78 100644 --- a/src/main/resources/sql/H2RegistrationDAOTest_populate.sql +++ b/src/main/resources/sql/H2RegistrationDAOTest_populate.sql @@ -4,7 +4,7 @@ INSERT INTO EmployeeVersion (id, name, birthday, educationLevel, isDriver, isPil INSERT INTO Employee (id, version) VALUES (1, 1); INSERT INTO Employee (id, version) VALUES (2, 2); INSERT INTO Employee (id, version) VALUES (3, 3); -INSERT INTO VehicleVersion (id, name, constructionType, type) VALUES (1, 'RTW-1', 'Hochdach', 'RTW'); -INSERT INTO VehicleVersion (id, name, constructionType, type) VALUES (2, 'NEF-1', 'Normal', 'NEF'); +INSERT INTO VehicleVersion (id, name, hasNef, constructionType, type) VALUES (1, 'RTW-1', TRUE, 'Hochdach', 'RTW'); +INSERT INTO VehicleVersion (id, name, hasNef, constructionType, type) VALUES (2, 'NEF-1', FALSE, 'Normal', 'NEF'); INSERT INTO Vehicle (id, version, status) VALUES (1, 1, 'abgemeldet'); INSERT INTO Vehicle (id, version, status) VALUES (2, 2, 'abgemeldet'); \ No newline at end of file -- cgit v1.2.3-70-g09d2 From ad44dc581b063485eede5c2b19b1b5ab0753ce72 Mon Sep 17 00:00:00 2001 From: Dominic Rogetzer Date: Sat, 5 May 2018 15:45:15 +0200 Subject: Change enum types to varchar with check constraint to support DBUnit --- src/main/resources/sql/database.sql | 17 +++++++++++------ 1 file changed, 11 insertions(+), 6 deletions(-) (limited to 'src/main/resources/sql') diff --git a/src/main/resources/sql/database.sql b/src/main/resources/sql/database.sql index bb23c91..e775550 100644 --- a/src/main/resources/sql/database.sql +++ b/src/main/resources/sql/database.sql @@ -1,26 +1,30 @@ CREATE TABLE IF NOT EXISTS VehicleVersion ( id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, - constructionType ENUM('Normal', 'Hochdach', 'Mittelhochdach') NOT NULL, - type ENUM('BKTW', 'KTW-B', 'KTW', 'RTW', 'NEF', 'NAH') NOT NULL, + constructionType VARCHAR NOT NULL, + type VARCHAR 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 ENUM('abgemeldet', 'frei_wache', 'zum_berufungsort', 'am_berufungsort', 'zum_zielort', - 'am_zielort', 'frei_funk', 'deleted') NOT NULL, + status VARCHAR 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 ENUM('RS', 'NFS', 'NKV', 'NKA', 'NKI', 'NA') NOT NULL, + educationLevel VARCHAR 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 ( @@ -43,10 +47,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 ENUM('A', 'B', 'C', 'D', 'E', 'O') NOT NULL, + severity VARCHAR NOT NULL, created TIMESTAMP NOT NULL, destination VARCHAR(100) NOT NULL, additionalInfo VARCHAR(100), + CHECK severity IN ('A', 'B', 'C', 'D', 'E', 'O') ); CREATE TABLE IF NOT EXISTS VehicleOperation ( -- cgit v1.2.3-70-g09d2 From d4d8ba884a80d0f7483efe2fb2c981023f983022 Mon Sep 17 00:00:00 2001 From: Felix Kehrer Date: Mon, 7 May 2018 17:23:31 +0200 Subject: Changed enums (and similar fields) to match enum names of Java --- .../einsatzverwaltung/dao/RegistrationDatabaseDAO.java | 2 +- src/main/resources/sql/H2RegistrationDAOTest_populate.sql | 8 ++++---- src/main/resources/sql/database.sql | 10 ++++++---- 3 files changed, 11 insertions(+), 9 deletions(-) (limited to 'src/main/resources/sql') diff --git a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/RegistrationDatabaseDAO.java b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/RegistrationDatabaseDAO.java index e4bc0ab..8fbcd18 100644 --- a/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/RegistrationDatabaseDAO.java +++ b/src/main/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/RegistrationDatabaseDAO.java @@ -25,7 +25,7 @@ public class RegistrationDatabaseDAO implements RegistrationDAO { private static final String ADD_REGISTRATION = "INSERT INTO Registration (vehicleId, employeeId, start, end, active) VALUES (?,?,?,?,?);"; private static final String UPDATE_VEHICLE = - "UPDATE Vehicle SET status = 'frei_wache' WHERE id = ?;"; + "UPDATE Vehicle SET status = 'FREI_WACHE' WHERE id = ?;"; private PreparedStatement addRegistration; private PreparedStatement updateVehicle; diff --git a/src/main/resources/sql/H2RegistrationDAOTest_populate.sql b/src/main/resources/sql/H2RegistrationDAOTest_populate.sql index b81eb78..3c268a0 100644 --- a/src/main/resources/sql/H2RegistrationDAOTest_populate.sql +++ b/src/main/resources/sql/H2RegistrationDAOTest_populate.sql @@ -4,7 +4,7 @@ INSERT INTO EmployeeVersion (id, name, birthday, educationLevel, isDriver, isPil INSERT INTO Employee (id, version) VALUES (1, 1); INSERT INTO Employee (id, version) VALUES (2, 2); INSERT INTO Employee (id, version) VALUES (3, 3); -INSERT INTO VehicleVersion (id, name, hasNef, constructionType, type) VALUES (1, 'RTW-1', TRUE, 'Hochdach', 'RTW'); -INSERT INTO VehicleVersion (id, name, hasNef, constructionType, type) VALUES (2, 'NEF-1', FALSE, 'Normal', 'NEF'); -INSERT INTO Vehicle (id, version, status) VALUES (1, 1, 'abgemeldet'); -INSERT INTO Vehicle (id, version, status) VALUES (2, 2, 'abgemeldet'); \ No newline at end of file +INSERT INTO VehicleVersion (id, name, hasNef, constructionType, type) VALUES (1, 'RTW-1', TRUE, 'HOCHDACH', 'RTW'); +INSERT INTO VehicleVersion (id, name, hasNef, constructionType, type) VALUES (2, 'NEF-1', FALSE, 'NORMAL', 'NEF'); +INSERT INTO Vehicle (id, version, status) VALUES (1, 1, 'ABGEMELDET'); +INSERT INTO Vehicle (id, version, status) VALUES (2, 2, 'ABGEMELDET'); \ No newline at end of file diff --git a/src/main/resources/sql/database.sql b/src/main/resources/sql/database.sql index e775550..4f3adf7 100644 --- a/src/main/resources/sql/database.sql +++ b/src/main/resources/sql/database.sql @@ -4,7 +4,7 @@ CREATE TABLE IF NOT EXISTS VehicleVersion ( constructionType VARCHAR NOT NULL, type VARCHAR NOT NULL, hasNef BOOLEAN NOT NULL, - CHECK constructionType IN ('Normal', 'Hochdach', 'Mittelhochdach'), + CHECK constructionType IN ('NORMAL', 'HOCHDACH', 'MITTELHOCHDACH'), CHECK type IN ('BKTW', 'KTW-B', 'KTW', 'RTW', 'NEF', 'NAH') ); @@ -13,8 +13,8 @@ CREATE TABLE IF NOT EXISTS Vehicle ( version BIGINT NOT NULL, status VARCHAR 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') + CHECK status IN ('ABGEMELDET', 'FREI_WACHE', 'ZUM_BERUFUNGSORT', 'AM_BERUFUNGSORT', 'ZUM_ZIELORT', + 'AM_ZIELORT', 'FREI_FUNK', 'DELETED') ); CREATE TABLE IF NOT EXISTS EmployeeVersion ( @@ -51,7 +51,9 @@ CREATE TABLE IF NOT EXISTS Operation ( created TIMESTAMP NOT NULL, destination VARCHAR(100) NOT NULL, additionalInfo VARCHAR(100), - CHECK severity IN ('A', 'B', 'C', 'D', 'E', 'O') + status VARCHAR NOT NULL, + CHECK severity IN ('A', 'B', 'C', 'D', 'E', 'O'), + CHECK status IN ('ACTIVE', 'COMPLETED', 'CANCELLED') ); CREATE TABLE IF NOT EXISTS VehicleOperation ( -- cgit v1.2.3-70-g09d2 From 834f9b4fff11c778dbb09dc74a88d658fc094a54 Mon Sep 17 00:00:00 2001 From: Felix Kehrer Date: Mon, 7 May 2018 18:06:00 +0200 Subject: Changed test behaviour to leave "clean" database for other tests --- .../sql/H2RegistrationDAOTest_populate.sql | 5 +++++ .../dao/RegistrationDatabaseDAOTest.java | 24 ++++++++++++++-------- 2 files changed, 20 insertions(+), 9 deletions(-) (limited to 'src/main/resources/sql') diff --git a/src/main/resources/sql/H2RegistrationDAOTest_populate.sql b/src/main/resources/sql/H2RegistrationDAOTest_populate.sql index 3c268a0..7e7b428 100644 --- a/src/main/resources/sql/H2RegistrationDAOTest_populate.sql +++ b/src/main/resources/sql/H2RegistrationDAOTest_populate.sql @@ -1,3 +1,8 @@ +DELETE FROM Registration; +DELETE FROM Vehicle; +DELETE FROM VehicleVersion; +DELETE FROM Employee; +DELETE FROM EmployeeVersion; INSERT INTO EmployeeVersion (id, name, birthday, educationLevel, isDriver, isPilot) VALUES (1, 'John Doe', '2000-01-01', 'RS', TRUE, TRUE); INSERT INTO EmployeeVersion (id, name, birthday, educationLevel, isDriver, isPilot) VALUES (2, 'Nick "Kage" Verily', '1990-01-01', 'NKV', TRUE, FALSE); INSERT INTO EmployeeVersion (id, name, birthday, educationLevel, isDriver, isPilot) VALUES (3, 'Nicht Arzt', '1980-01-01', 'NA', FALSE, FALSE); diff --git a/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/RegistrationDatabaseDAOTest.java b/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/RegistrationDatabaseDAOTest.java index 980c429..03059ff 100644 --- a/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/RegistrationDatabaseDAOTest.java +++ b/src/test/java/at/ac/tuwien/sepm/assignment/groupphase/einsatzverwaltung/dao/RegistrationDatabaseDAOTest.java @@ -14,8 +14,7 @@ import java.time.LocalDate; import java.util.LinkedList; import java.util.List; import org.h2.tools.RunScript; -import org.junit.After; -import org.junit.Before; +import org.junit.AfterClass; import org.junit.BeforeClass; import org.junit.Rule; import org.junit.Test; @@ -45,10 +44,6 @@ public class RegistrationDatabaseDAOTest { "classpath:sql/database.sql", Charset.forName("UTF8"), false); - } - - @Before - public void setUp() throws SQLException { RunScript.execute( JDBC_URL, USER, @@ -57,9 +52,20 @@ public class RegistrationDatabaseDAOTest { Charset.forName("UTF8"), false); } - - @After - public void tearDown() throws SQLException { + /* + @Before + public void setUp() throws SQLException { + RunScript.execute( + JDBC_URL, + USER, + PASSWORD, + "classpath:sql/H2RegistrationDAOTest_populate.sql", + Charset.forName("UTF8"), + false); + } + */ + @AfterClass + public static void tearDown() throws SQLException { RunScript.execute( JDBC_URL, USER, -- cgit v1.2.3-70-g09d2