3 files changed, 34 insertions, 6 deletions
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..7e7b428
--- /dev/null
+++ b/src/main/resources/sql/H2RegistrationDAOTest_populate.sql
@@ -0,0 +1,15 @@
+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);
+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
diff --git a/src/main/resources/sql/database.sql b/src/main/resources/sql/database.sql
index 9d1b0e1..4f3adf7 100644
--- a/src/main/resources/sql/database.sql
+++ b/src/main/resources/sql/database.sql
@@ -1,25 +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 (
@@ -42,10 +47,13 @@ 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),
+  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 (