hostbbq-monitoring/server/mysql/init-db.sql

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+01:00";

CREATE DATABASE IF NOT EXISTS `hbbq-monitoring` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'monitor'@'%' IDENTIFIED BY 'i4mGr00ti4mGr00t';
    
USE `hbbq-monitoring`;
GRANT ALL PRIVILEGES ON `hbbq-monitoring`.* TO 'monitor'@'%' IDENTIFIED BY 'i4mGr00ti4mGr00t';

CREATE TABLE `Server` (
  `ID` INT PRIMARY KEY AUTO_INCREMENT,
  `Title` VARCHAR(64) NOT NULL,
  `FQDN` VARCHAR(512) NOT NULL,
  UNIQUE INDEX UQ_Server_Title (`Title`),
  UNIQUE INDEX UQ_Server_FQDN (`FQDN`)
);

CREATE TABLE `HealthCheckConfig` (
  `ID` INT PRIMARY KEY AUTO_INCREMENT,
  `ServerID` INT NOT NULL,
  `Type` VARCHAR(16) NOT NULL,
  `Title` VARCHAR(512) NOT NULL,
  CONSTRAINT FK_HealthCheckConfig_Server FOREIGN KEY(`ServerID`) REFERENCES `Server`(`ID`) ON DELETE CASCADE,
  UNIQUE INDEX UQ_HealthCheckConfig_1 (`ServerID`, `Type`, `Title`)
);

CREATE TABLE `HealthCheckParams` (
  `ID` INT PRIMARY KEY AUTO_INCREMENT,
  `ConfigID` INT NOT NULL,
  `Type` VARCHAR(16) NOT NULL,
  `Key` VARCHAR(32) NOT NULL,
  `Value` TEXT NOT NULL,
  CONSTRAINT FK_HealthCheckParams_HealthCheckConfig FOREIGN KEY(`ConfigID`) REFERENCES `HealthCheckConfig`(`ID`) ON DELETE CASCADE
);

CREATE TABLE `HealthCheckDataEntry` (
  `ID` BIGINT PRIMARY KEY AUTO_INCREMENT,
  `ConfigID` INT NOT NULL,
  `Timestamp` TIMESTAMP NOT NULL,
  `Status` SMALLINT NOT NULL,
  `Message` TEXT,
  CONSTRAINT FK_HealthCheckDataEntry_HealthCheckConfig FOREIGN KEY(`ConfigID`) REFERENCES `HealthCheckConfig`(`ID`) ON DELETE CASCADE
);

CREATE TABLE `ServerConfig` (
  `ID` INT PRIMARY KEY AUTO_INCREMENT,
  `ServerID` INT NOT NULL,
  `Key` VARCHAR(32) NOT NULL,
  `Value` TEXT NOT NULL,
  CONSTRAINT FK_ServerConfig_Server FOREIGN KEY(`ServerID`) REFERENCES `Server`(`ID`) ON DELETE CASCADE,
  UNIQUE INDEX UQ_ServerConfig_1(`ServerID`, `Key`)
);

CREATE TABLE `ServerDataEntry` (
  `ID` BIGINT PRIMARY KEY AUTO_INCREMENT,
  `ServerID` INT NOT NULL,
  `Timestamp` TIMESTAMP NOT NULL,
  CONSTRAINT FK_ServerDataEntry_Server FOREIGN KEY(`ServerID`) REFERENCES `Server`(`ID`) ON DELETE CASCADE,
  UNIQUE INDEX UQ_ServerDataEntry_1(`ServerID`, `Timestamp`)
);

CREATE TABLE `ServerDataValue` (
  `ID` BIGINT PRIMARY KEY AUTO_INCREMENT,
  `EntryID` BIGINT NOT NULL,
  `Type` VARCHAR(512) NOT NULL,
  `Key` VARCHAR(32) NOT NULL,
  `Value` DOUBLE NOT NULL,
  CONSTRAINT FK_ServerDataValue_ServerDataEntry FOREIGN KEY(`EntryID`) REFERENCES `ServerDataEntry`(`ID`) ON DELETE CASCADE,
  UNIQUE INDEX UQ_ServerDataValue_1(`EntryID`, `Type`, `Key`)
);

COMMIT;
SET AUTOCOMMIT = 1;