Revize 199b5c55

Re #7385 Initialisation of the database

server/init.sql

@@ -1 @@
-CREATE TABLE test (
-  id MEDIUMINT NOT NULL AUTO_INCREMENT,
-  message VARCHAR(255),
+-- -----------------------------------------------------
+-- Table role
+-- -----------------------------------------------------
+DROP TABLE IF EXISTS role;
+CREATE TABLE role (
+  id TINYINT NOT NULL AUTO_INCREMENT,
+  name VARCHAR(8) NOT NULL,
   PRIMARY KEY (id)
 );
 
-INSERT INTO test (message) VALUES ('test 1');
-INSERT INTO test (message) VALUES ('test 2');
-INSERT INTO test (message) VALUES ('test 3');
-INSERT INTO test (message) VALUES ('test 4');
+-- -----------------------------------------------------
+-- Table approval_status
+-- -----------------------------------------------------
+DROP TABLE IF EXISTS approval_status;
+CREATE TABLE approval_status (
+  id TINYINT NOT NULL AUTO_INCREMENT,
+  name VARCHAR(8) NOT NULL,
+  PRIMARY KEY (id)
+);
+
+-- -----------------------------------------------------
+-- Table vacation_type
+-- -----------------------------------------------------
+DROP TABLE IF EXISTS vacation_type;
+CREATE TABLE vacation_type (
+  id TINYINT NOT NULL AUTO_INCREMENT,
+  name VARCHAR(8) NOT NULL,
+  PRIMARY KEY (id)
+);
+
+-- -----------------------------------------------------
+-- Table end_user
+-- -----------------------------------------------------
+DROP TABLE IF EXISTS end_user;
+CREATE TABLE end_user (
+  id BIGINT NOT NULL AUTO_INCREMENT,
+  first_name VARCHAR(45) NOT NULL,
+  last_name VARCHAR(45) NOT NULL,
+  no_vacations FLOAT,
+  no_sick_days SMALLINT,
+  alert DATETIME,
+  token TEXT NOT NULL,
+  email VARCHAR(45) NOT NULL,
+  photo TEXT,
+  creation_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  role_id TINYINT NOT NULL,
+  status_id TINYINT NOT NULL,
+  PRIMARY KEY (id),
+  INDEX fk_end_user_role (role_id ASC),
+  INDEX fk_end_user_approval_status (status_id ASC),
+  CONSTRAINT fk_end_user_role FOREIGN KEY (role_id)
+    REFERENCES role (id)
+    ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT fk_end_user_approval_status FOREIGN KEY (status_id)
+    REFERENCES approval_status (id)
+    ON DELETE NO ACTION ON UPDATE CASCADE,
+  CONSTRAINT check_no_vacation CHECK (no_vacations >= 0),
+  CONSTRAINT check_no_sick_days CHECK (no_sick_days >= 0),
+  CONSTRAINT check_email CHECK (email = '%_@__%.__%')
+);
+
+-- -----------------------------------------------------
+-- Table vacation_day
+-- -----------------------------------------------------
+DROP TABLE IF EXISTS vacation_day;
+CREATE TABLE vacation_day (
+  id BIGINT NOT NULL AUTO_INCREMENT,
+  vacation_date DATE NOT NULL,
+  time_from TIME,
+  time_to TIME,
+  creation_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  user_id BIGINT NOT NULL,
+  status_id TINYINT NOT NULL,
+  type_id TINYINT NOT NULL,
+  PRIMARY KEY (id),
+  INDEX fk_vacation_day_approval_status (status_id ASC),
+  INDEX fk_vacation_day_end_user (user_id ASC),
+  CONSTRAINT fk_vacation_day_approval_status FOREIGN KEY (status_id)
+    REFERENCES approval_status (id)
+    ON DELETE NO ACTION ON UPDATE CASCADE,
+  CONSTRAINT fk_vacation_day_end_user FOREIGN KEY (user_id)
+    REFERENCES end_user (id)
+    ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT check_time CHECK (time_from < time_to)
+);
+
+-- -----------------------------------------------------
+-- Table Default_Settings
+-- -----------------------------------------------------
+DROP TABLE IF EXISTS default_settings;
+CREATE TABLE default_settings (
+  id BIGINT NOT NULL AUTO_INCREMENT,
+  no_sick_days SMALLINT NOT NULL,
+  alert DATETIME NOT NULL,
+  creation_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  PRIMARY KEY (id),
+  CONSTRAINT check_no_sick_days CHECK (no_sick_days >= 0)
+);
+
+-- -----------------------------------------------------
+-- Insert table role
+-- -----------------------------------------------------
+INSERT INTO role (name) VALUES ('employee');
+INSERT INTO role (name) VALUES ('employer');
+
+-- -----------------------------------------------------
+-- Insert table approval_status
+-- -----------------------------------------------------
+INSERT INTO approval_status (name) VALUES ('accepted');
+INSERT INTO approval_status (name) VALUES ('pending');
+INSERT INTO approval_status (name) VALUES ('rejected');
+
+-- -----------------------------------------------------
+-- Insert table vacation_type
+-- -----------------------------------------------------
+INSERT INTO vacation_type (name) VALUES ('sickday');
+INSERT INTO vacation_type (name) VALUES ('vacation');
+
+-- -----------------------------------------------------
+-- Insert table end_user
+-- -----------------------------------------------------
+-- INSERT INTO end_user (first_name, last_name, no_vacations, no_sick_days, alert, token, email, photo, role_id, status_id) VALUES ();
+
+-- -----------------------------------------------------
+-- Insert table vacation_day
+-- -----------------------------------------------------
+-- INSERT INTO vacation_day (vacation_date, time_from, time_to, user_id, status_id, type_id) VALUES ();
+
+-- -----------------------------------------------------
+-- Insert table default_settings
+-- -----------------------------------------------------
+INSERT INTO default_settings (no_sick_days, alert) VALUES (5, '2019-12-01 12:00:00.000');