102 lines
3.7 KiB
SQL
102 lines
3.7 KiB
SQL
-- init.sql
|
|
CREATE DATABASE IF NOT EXISTS elio_test;
|
|
CREATE DATABASE IF NOT EXISTS elio;
|
|
|
|
|
|
-- Haupttabellen erzeugen
|
|
-- CREATE TABLE IF NOT EXISTS elio.blablabla (
|
|
-- id INT AUTO_INCREMENT PRIMARY KEY,
|
|
-- warehouse char(2) NOT NULL,
|
|
-- productid VARCHAR(20) NOT NULL,
|
|
-- amount INT DEFAULT 0,
|
|
-- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
-- CONSTRAINT location_product_must_be_one UNIQUE (warehouse, productid)
|
|
-- ) ENGINE=InnoDB;
|
|
|
|
|
|
|
|
-- Doppelter Boden: Tabellen werden zur Not auch beim Starten des Services im ersten Datenbank Call erzeugt
|
|
CREATE TABLE IF NOT EXISTS elio.warehouseproducts (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
warehouse char(2) NOT NULL,
|
|
productid VARCHAR(20) NOT NULL,
|
|
amount INT DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT location_product_must_be_one UNIQUE (warehouse, productid)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS elio.deliverytimes (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
fromcountry varchar(4) NOT NULL,
|
|
tocountry varchar(4) NOT NULL,
|
|
state varchar(4) NULL,
|
|
delivery INT DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT delivery_from_to_country_must_be_one UNIQUE (fromcountry, tocountry)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS elio.reservations (
|
|
id CHAR(36) PRIMARY KEY DEFAULT UUID(),
|
|
deliveryId INT,
|
|
warehouseId INT,
|
|
amount INT DEFAULT 0,
|
|
status VARCHAR(30) CHECK(status IN ('RESERVED', 'CONFIRMED', 'ABORTED', 'RELEASED')),
|
|
reservationGroupId CHAR(36) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_delivery_id
|
|
FOREIGN KEY (deliveryId) REFERENCES deliverytimes (id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE RESTRICT,
|
|
CONSTRAINT fk_warehouse_id
|
|
FOREIGN KEY (warehouseId) REFERENCES warehouseproducts (id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE RESTRICT
|
|
) ENGINE=InnoDB;
|
|
|
|
-- Testtabellen erzeugen
|
|
CREATE TABLE IF NOT EXISTS elio_test.warehouseproducts (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
warehouse char(2) NOT NULL,
|
|
productid VARCHAR(20) NOT NULL,
|
|
amount INT DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT location_product_must_be_one UNIQUE (warehouse, productid)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS elio_test.deliverytimes (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
fromcountry varchar(4) NOT NULL,
|
|
tocountry varchar(4) NOT NULL,
|
|
state varchar(4) NULL,
|
|
delivery INT DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT delivery_from_to_country_must_be_one UNIQUE (fromcountry, tocountry)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS elio_test.reservations (
|
|
id CHAR(36) PRIMARY KEY DEFAULT UUID(),
|
|
deliveryId INT,
|
|
warehouseId INT,
|
|
amount INT DEFAULT 0,
|
|
status VARCHAR(30) CHECK(status IN ('RESERVED', 'CONFIRMED', 'ABORTED', 'RELEASED')),
|
|
reservationGroupId CHAR(36) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_delivery_id
|
|
FOREIGN KEY (deliveryId) REFERENCES deliverytimes (id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE RESTRICT,
|
|
CONSTRAINT fk_warehouse_id
|
|
FOREIGN KEY (warehouseId) REFERENCES warehouseproducts (id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE RESTRICT
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
|
|
-- Userkram
|
|
CREATE USER IF NOT EXISTS 'elio'@'%' IDENTIFIED BY 'eliogeheim';
|
|
CREATE USER IF NOT EXISTS 'elio_test'@'%' IDENTIFIED BY 'eliogeheim';
|
|
GRANT ALL PRIVILEGES ON elio.* TO 'elio'@'%';
|
|
GRANT ALL PRIVILEGES ON elio_test.* TO 'elio_test'@'%';
|
|
FLUSH PRIVILEGES;
|