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/database.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/database.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 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/database.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/database.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