139 lines
6.2 KiB
SQL
139 lines
6.2 KiB
SQL
CREATE DATABASE IF NOT EXISTS mmcl_db;
|
|
USE mmcl_db;
|
|
|
|
CREATE TABLE IF NOT EXISTS `pos_info` (
|
|
`pos_id` VARCHAR(15) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
|
|
`pos_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
|
|
INDEX `pos_id` (`pos_id`) USING BTREE
|
|
) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS `type_info` (
|
|
`dev_typeID` INT(11) NULL DEFAULT NULL,
|
|
`dev_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
|
|
INDEX `dev_typeID` (`dev_typeID`) USING BTREE
|
|
) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS `status_info` (
|
|
`sts_id` INT(11) NULL DEFAULT NULL,
|
|
`sts_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
|
|
`sts_level` INT(11) NULL DEFAULT NULL,
|
|
INDEX `sts_id` (`sts_id`) USING BTREE
|
|
) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS `dev_info` (
|
|
`dev_no` INT(11) NOT NULL AUTO_INCREMENT,
|
|
`dev_id` VARCHAR(15) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
|
|
`dev_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
|
|
`dev_desc` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
|
|
`dev_posID` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
|
|
`led_sensorNo` INT(11) NULL DEFAULT NULL,
|
|
`nilm_sensorNo` INT(11) NULL DEFAULT NULL,
|
|
`nilm_chNo` INT(11) NULL DEFAULT NULL,
|
|
UNIQUE INDEX `dev_no` (`dev_no`) USING BTREE,
|
|
UNIQUE INDEX `dev_id` (`dev_id`) USING BTREE,
|
|
UNIQUE INDEX `dev_name` (`dev_name`) USING BTREE
|
|
) COMMENT='NILM장치가 연결된 설비의 정보' COLLATE='utf8mb4_general_ci' ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS `nilm_init_value` (
|
|
`dev_no` INT(11) NULL DEFAULT NULL,
|
|
`ch1_current` FLOAT NULL DEFAULT NULL,
|
|
`ch1_volt` FLOAT NULL DEFAULT NULL,
|
|
`ch2_current` FLOAT NULL DEFAULT NULL,
|
|
`ch2_volt` FLOAT NULL DEFAULT NULL,
|
|
`ch3_current` FLOAT NULL DEFAULT NULL,
|
|
`ch3_volt` FLOAT NULL DEFAULT NULL,
|
|
INDEX `nilm_no` (`dev_no`) USING BTREE
|
|
) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS `sensor_info` (
|
|
`sensor_no` INT(11) NOT NULL AUTO_INCREMENT,
|
|
`sensor_id` VARCHAR(15) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
|
|
`sensor_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
|
|
`_desc` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
|
|
`sensor_typeid` INT(11) NOT NULL COMMENT '1:NILM, 2:경광등',
|
|
`update_time` DATETIME NULL DEFAULT NULL,
|
|
`mqtt_topic` VARCHAR(255) NULL DEFAULT NULL COMMENT 'NILM 센서 연동용 MQTT 토픽',
|
|
|
|
-- Current Status / Values
|
|
`value_pf` FLOAT NULL DEFAULT NULL,
|
|
`value_ch1_current` FLOAT NULL DEFAULT NULL,
|
|
`value_ch1_volt` FLOAT NULL DEFAULT NULL,
|
|
`value_ch1_pwr` FLOAT NULL DEFAULT NULL,
|
|
`value_ch2_current` FLOAT NULL DEFAULT NULL,
|
|
`value_ch2_volt` FLOAT NULL DEFAULT NULL,
|
|
`value_ch2_pwr` FLOAT NULL DEFAULT NULL,
|
|
`value_ch3_current` FLOAT NULL DEFAULT NULL,
|
|
`value_ch3_volt` FLOAT NULL DEFAULT NULL,
|
|
`value_ch3_pwr` FLOAT NULL DEFAULT NULL,
|
|
|
|
`value_ch1_statusID` INT(11) NULL DEFAULT NULL COMMENT 'type이 2 일때 이값은 Green LED 상태값',
|
|
`value_ch2_statusID` INT(11) NULL DEFAULT NULL COMMENT 'type이 2일때 이값은 Yellow LED의 상태',
|
|
`value_ch3_statusID` INT(11) NULL DEFAULT NULL COMMENT 'type이 2일때 이값은 Red LED의 상태',
|
|
|
|
-- Target Status (New fields for handshake)
|
|
`target_ch1_statusID` INT(11) NULL DEFAULT NULL,
|
|
`target_ch2_statusID` INT(11) NULL DEFAULT NULL,
|
|
`target_ch3_statusID` INT(11) NULL DEFAULT NULL,
|
|
`last_updated_by` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
|
|
`led_ip` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
|
|
`led_port` INT(11) NULL DEFAULT 20000,
|
|
|
|
UNIQUE INDEX `nilm_no` (`sensor_no`) USING BTREE,
|
|
UNIQUE INDEX `sensor_id` (`sensor_id`) USING BTREE,
|
|
INDEX `dev_no` (`sensor_no`) USING BTREE
|
|
) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS `sensor_history_log` (
|
|
`log_no` INT(11) NOT NULL AUTO_INCREMENT,
|
|
`sensor_no` INT(11) NOT NULL,
|
|
`log_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
`value_ch1_current` FLOAT NULL DEFAULT NULL,
|
|
`value_ch1_volt` FLOAT NULL DEFAULT NULL,
|
|
`value_ch1_pwr` FLOAT NULL DEFAULT NULL,
|
|
`value_ch2_current` FLOAT NULL DEFAULT NULL,
|
|
`value_ch2_volt` FLOAT NULL DEFAULT NULL,
|
|
`value_ch2_pwr` FLOAT NULL DEFAULT NULL,
|
|
`value_ch3_current` FLOAT NULL DEFAULT NULL,
|
|
`value_ch3_volt` FLOAT NULL DEFAULT NULL,
|
|
`value_ch3_pwr` FLOAT NULL DEFAULT NULL,
|
|
PRIMARY KEY (`log_no`),
|
|
INDEX `idx_sensor_time` (`sensor_no`, `log_time`) USING BTREE
|
|
) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS `ai_control_log` (
|
|
`log_id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
`req_text` TEXT NULL DEFAULT NULL,
|
|
`dev_no` INT(11) NOT NULL,
|
|
`target_val` VARCHAR(255) NULL DEFAULT NULL,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`log_id`),
|
|
INDEX `dev_no` (`dev_no`) USING BTREE,
|
|
CONSTRAINT `fk_ai_log_dev` FOREIGN KEY (`dev_no`) REFERENCES `dev_info` (`dev_no`)
|
|
) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB COMMENT='AI 제어 로그';
|
|
|
|
CREATE TABLE IF NOT EXISTS `event_log` (
|
|
`event_id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
`dev_no` INT(11) NOT NULL,
|
|
`event_type` VARCHAR(50) NOT NULL,
|
|
`status` VARCHAR(20) NOT NULL,
|
|
`occurred_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`resolved_at` DATETIME NULL DEFAULT NULL,
|
|
PRIMARY KEY (`event_id`),
|
|
INDEX `dev_no` (`dev_no`) USING BTREE,
|
|
CONSTRAINT `fk_event_log_dev` FOREIGN KEY (`dev_no`) REFERENCES `dev_info` (`dev_no`)
|
|
) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB COMMENT='알림/이벤트 로그';
|
|
|
|
-- Seed Data (For Testing)
|
|
INSERT IGNORE INTO type_info (dev_typeID, dev_name) VALUES (1, 'NILM'), (2, '경광등');
|
|
INSERT IGNORE INTO status_info (sts_id, sts_name, sts_level) VALUES (0, 'OFF', 0), (1, 'ON', 1);
|
|
INSERT IGNORE INTO pos_info (pos_id, pos_name) VALUES ('POS_01', 'A동 1구역');
|
|
|
|
-- Sensors
|
|
INSERT IGNORE INTO sensor_info (sensor_no, sensor_id, sensor_name, sensor_typeid, value_ch1_statusID, target_ch1_statusID, led_ip, led_port) VALUES
|
|
(101, 'S_NILM_01', 'CNC NILM', 1, NULL, NULL, NULL, NULL),
|
|
(102, 'S_LED_01', 'CNC 경광등', 2, 0, 0, '192.168.0.100', 20000);
|
|
|
|
-- Device
|
|
INSERT IGNORE INTO dev_info (dev_no, dev_id, dev_name, dev_desc, dev_posID, led_sensorNo, nilm_sensorNo, nilm_chNo) VALUES
|
|
(1, 'D_CNC_01', 'CNC 선반 1호기', '메인 머신', 'POS_01', 102, 101, 1);
|