setup.sql

SET GLOBAL sql_mode="STRICT_TRANS_TABLES";

CREATE DATABASE IF NOT EXISTS lamp_controller;
USE lamp_controller;

CREATE TABLE IF NOT EXISTS user (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_name CHAR(32) NOT NULL UNIQUE,
    display_name CHAR(32) NOT NULL,
    password_hash CHAR(255) NOT NULL,
    software_key CHAR(255) NOT NULL,
    last_updated_at BIGINT UNSIGNED NOT NULL,
    last_logdin_at BIGINT UNSIGNED NOT NULL,
    flag INT UNSIGNED DEFAULT 0 NOT NULL)
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS directory (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_id BIGINT,
    user_id INT UNSIGNED NOT NULL,
    flag INT UNSIGNED DEFAULT 0 NOT NULL,
    INDEX(parent_id),
    FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE)
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS file (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    directory_id BIGINT NOT NULL,
    flag INT UNSIGNED DEFAULT 0 NOT NULL,
    INDEX(directory_id),
    FOREIGN KEY (directory_id) REFERENCES directory (id) ON DELETE CASCADE)
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS software (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    drive_serial_no INT NOT NULL,
    pc_name CHAR(100) NOT NULL,
    entered_at BIGINT NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    display_name CHAR(32) NOT NULL,
    flag INT UNSIGNED NOT NULL DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE)
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS send_queue (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    milli_time BIGINT NOT NULL,
    software_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    json TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE,
    FOREIGN KEY (software_id) REFERENCES software (id) ON DELETE CASCADE)
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS receive (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    software_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    time BIGINT NOT NULL,
    json TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE,
    FOREIGN KEY (software_id) REFERENCES software (id) ON DELETE CASCADE)
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS session (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    session_id CHAR(255) NOT NULL UNIQUE,
    user_id INT UNSIGNED NOT NULL,
    type TINYINT UNSIGNED NOT NULL,
    last_logdin_at BIGINT UNSIGNED NOT NULL,
    flag INT UNSIGNED DEFAULT 0 NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE)
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS guest (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    guest_id CHAR(255) NOT NULL UNIQUE,	
    display_name CHAR(32) NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    software_id INT UNSIGNED NOT NULL,
    flag INT UNSIGNED DEFAULT 0 NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE,
    FOREIGN KEY (software_id) REFERENCES software (id) ON DELETE CASCADE)
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;