Add sql database schema

1 files changed, 57 insertions, 0 deletions

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),
+);