From 708ed158c41ee2ee5dfce64fdecf89aed4c17685 Mon Sep 17 00:00:00 2001
From: Tharre <tharre3@gmail.com>
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 <tharre3@gmail.com>
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 <e1627756@student.tuwien.ac.at>
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 <felix.kehrer@gmail.com>
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

