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

