log_mysql_schema.txt

DROP DATABASE IF EXISTS msg_log;

CREATE DATABASE msg_log;

USE msg_log;

SET default_storage_engine=INNODB;

-- Create 'msg_log' user who can remotely access the 'archive' tables,
-- but only change the table layout locally
DROP USER IF EXISTS 'msg_log'@'localhost';
CREATE USER 'msg_log'@'localhost' IDENTIFIED WITH caching_sha2_password BY '$msg_log';

DROP USER IF EXISTS 'msg_log'@'%';
CREATE USER 'msg_log'@'%' IDENTIFIED WITH caching_sha2_password BY '$msg_log';

GRANT ALL ON msg_log.* TO 'msg_log'@'localhost';
GRANT INSERT, SELECT, UPDATE, DELETE ON msg_log.* TO 'msg_log'@'%';
FLUSH PRIVILEGES;

-- Create 'msg_report' user who can read data
DROP USER IF EXISTS 'msg_report'@'localhost';
CREATE USER 'msg_report'@'localhost' IDENTIFIED WITH caching_sha2_password BY '$msg_report';
DROP USER IF EXISTS 'msg_report'@'%';
CREATE USER 'msg_report'@'%' IDENTIFIED WITH caching_sha2_password BY '$msg_report';

GRANT SELECT ON msg_log.* TO 'msg_report'@'localhost';
GRANT SELECT ON msg_log.* TO 'msg_report'@'%';
-- Default EPICS report user --
GRANT SELECT ON msg_log.* TO report@localhost;
GRANT SELECT ON msg_log.* TO report@'%';

FLUSH PRIVILEGES;

# id has fixed values - see inserts below
DROP TABLE IF EXISTS message_type;
CREATE TABLE message_type
(
    id INT NOT NULL,
    type VARCHAR(32) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE(type)
);

# id has fixed values - see inserts below
DROP TABLE IF EXISTS message_severity;
CREATE TABLE message_severity
(
    id INT NOT NULL,
    severity VARCHAR(16) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE(severity)
);

# usually IOC name
DROP TABLE IF EXISTS client_name;
CREATE TABLE client_name
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(64) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE(name)
);

DROP TABLE IF EXISTS client_host;
CREATE TABLE client_host
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(64) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE(name)
);

# name of application writing to database 
DROP TABLE IF EXISTS application;
CREATE TABLE application
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(64) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE(name)
);

DROP TABLE IF EXISTS message;
CREATE TABLE message 
(
  id INT NOT NULL AUTO_INCREMENT,
  createTime TIMESTAMP(3) NULL,
  eventTime TIMESTAMP(3) NULL,
  type_id INT NOT NULL,     # from message_type table 
  contents TEXT NULL,
  clientName_id INT NULL,   # from client_name table 
  severity_id INT NOT NULL, # from message_severity table
  clientHost_id INT NULL,   # from client_host table 
  application_id INT NULL,  # from application table 
  repeatCount INT NULL DEFAULT 1,
  PRIMARY KEY (id),
  FOREIGN KEY(type_id) REFERENCES message_type(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY(severity_id) REFERENCES message_severity(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY(clientName_id) REFERENCES client_name(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY(clientHost_id) REFERENCES client_host(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY(application_id) REFERENCES application(id) ON DELETE CASCADE ON UPDATE CASCADE  
);

CREATE INDEX message_type_index ON message(type_id);
CREATE INDEX message_severity_index ON message(severity_id);
CREATE INDEX message_app_index ON message(application_id);
CREATE INDEX message_cn_index ON message(clientName_id);
CREATE INDEX message_ch_index ON message(clientHost_id);

# ids are fixed and used directly in log client for speed
INSERT INTO message_type VALUES (1, 'caput');
INSERT INTO message_type VALUES (2, 'ioclog');
INSERT INTO message_type VALUES (3, 'SIM_MSG');

# ids are fixed and used directly in log client for speed
INSERT INTO message_severity VALUES (1, 'INFO');
INSERT INTO message_severity VALUES (2, 'MINOR');
INSERT INTO message_severity VALUES (3, 'MAJOR');
INSERT INTO message_severity VALUES (4, 'FATAL');

# add some special fixed IDs that can be used for speed when writing
# other can be added by client inserts as needed
INSERT INTO application VALUES (1, 'IOCLogServer');

# add some special fixed IDs that can be used for speed when writing
# other can be added by client inserts as needed
INSERT INTO client_host VALUES (1, '127.0.0.1');

# Automatic log message table truncation
source create_event_logger.sql;
source debug_log.sql;
source binary-search.sql;
source truncate_message_table.sql
source truncate_event.sql;