DB 구축
테이블 생성
CREATE DATABASE mdc; USE mdc; -- CRAC CREATE TABLE `tb_crac_statusvalue` ( `CracID` VARCHAR(20) NOT NULL COMMENT 'CRAC ID', -- CRAC ID `RunMode` INT(11) NULL COMMENT 'CRAC Run Mode', -- CRAC Run Mode `StaticPressControl1` DECIMAL(20,5) NULL COMMENT 'Static Press Control 1', -- Static Press Control 1 `StaticPressControl2` DECIMAL(20,5) NULL COMMENT 'Static Press Control 2', -- Static Press Control 2 `DifferentialPressure1` DECIMAL(20,5) NULL COMMENT 'Differential Pressure 1', -- Differential Pressure 1 `DifferentialPressure2` DECIMAL(20,5) NULL COMMENT 'Differential Pressure 2', -- Differential Pressure 2 `HotZoneTemp1` DECIMAL(20,5) NULL COMMENT 'Hot Zone Temp', -- Hot Zone Temp `HotZoneTemp2` DECIMAL(20,5) NULL COMMENT 'Hot Zone Temp', -- Hot Zone Temp `HotZoneTemp3` DECIMAL(20,5) NULL COMMENT 'Hot Zone Temp', -- Hot Zone Temp `HotZoneTemp4` DECIMAL(20,5) NULL COMMENT 'Hot Zone Temp', -- Hot Zone Temp `RATemp` DECIMAL(20,5) NULL COMMENT 'RA Temp', -- RA Temp `RAHum` DECIMAL(20,5) NULL COMMENT 'RA Hum', -- RA Hum `OATemp` DECIMAL(20,5) NULL COMMENT 'OA Temp', -- OA Temp `OAHum` DECIMAL(20,5) NULL COMMENT 'OA Hum', -- OA Hum `CoolZoneTemp_1` DECIMAL(20,5) NULL COMMENT 'Cool Zone Temp', -- Cool Zone Temp `CoolZoneTemp_2` DECIMAL(20,5) NULL COMMENT 'Cool Zone Temp', -- Cool Zone Temp `CoolZoneHum_1` DECIMAL(20,5) NULL COMMENT 'Cool Zone Hum', -- Cool Zone Hum `CoolZoneHum_2` DECIMAL(20,5) NULL COMMENT 'Cool Zone Hum', -- Cool Zone Hum `CoolingCoilFedback` DECIMAL(20,5) NULL COMMENT 'Cooling coil feedback', -- Cooling coil feedback `CoolingCoilInOutTemp_1` DECIMAL(20,5) NULL COMMENT 'Cooling coil In/Out Temp', -- Cooling coil In/Out Temp `CoolingCoilInOutTemp_2` DECIMAL(20,5) NULL COMMENT 'Cooling coil In/Out Temp', -- Cooling coil In/Out Temp `AISpare_1` DECIMAL(20,5) NULL COMMENT 'AI _ Spare', -- AI _ Spare `AISpare_2` DECIMAL(20,5) NULL COMMENT 'AI _ Spare', -- AI _ Spare `SupplyFanStatus` VARCHAR(1) NULL COMMENT 'Supply Fan Status', -- Supply Fan Status `ExhaustFanStatus` VARCHAR(1) NULL COMMENT 'Exhaust Fan Run Stauts', -- Exhaust Fan Run Stauts `SupplyFanAlarm` VARCHAR(1) NULL COMMENT 'Supply Fan Alarm', -- Supply Fan Alarm `ExhaustFanAlarm` VARCHAR(1) NULL COMMENT 'Exhaust Fan Alarm', -- Exhaust Fan Alarm `RemoteFanStatus` VARCHAR(1) NULL COMMENT 'Remote Fan Run / Stop', -- Remote Fan Run / Stop `FireOrEmergency` VARCHAR(1) NULL COMMENT 'Fire or Emergency Stop', -- Fire or Emergency Stop `DISpare_1` VARCHAR(1) NULL COMMENT 'DI _ Spare', -- DI _ Spare `DISpare_2` VARCHAR(1) NULL COMMENT 'DI _ Spare', -- DI _ Spare `Date` DATETIME NULL COMMENT 'Data Store Time' -- Data Store Time ) COMMENT 'CRAC'; -- PDU CREATE TABLE `tb_pdu` ( `PduID` VARCHAR(20) NOT NULL COMMENT 'PDU ID', -- PDU ID `IPAddr` VARCHAR(20) NOT NULL COMMENT 'PDU IP Address' -- IP Address ) COMMENT 'PDU'; -- PDU Main CREATE TABLE `tb_pdu_mainstatusvalue` ( `PduID` VARCHAR(20) NOT NULL COMMENT 'PDU ID', -- PDU ID `MainCurrent` DECIMAL(20,5) NULL COMMENT 'Main Current', -- Main Current `MainIntegration` decimal(20,5) NULL COMMENT 'Main Integration', -- Main Integration `MainVoltage` DECIMAL(20,5) NULL COMMENT 'Main Voltage', -- Main Voltage `MainHz` DECIMAL(20,5) NULL COMMENT 'Main Hz', -- Main Hz `MainTemperature` DECIMAL(20,5) NULL COMMENT 'Main Temperature', -- Main Temperature `MainHumidity` DECIMAL(20,5) NULL COMMENT 'Main Humidity', -- Main Humidity `Date` DATETIME NOT NULL COMMENT 'Data Store Time' -- Data Store Time ) COMMENT 'PDU Main'; -- Rack Status Information CREATE TABLE `tb_rack_tempvalue` ( `PduID` VARCHAR(20) NOT NULL COMMENT 'PDU ID', -- PDU ID `FrontTopTemp` DECIMAL(20,5) NULL COMMENT 'Front Top Temperature', -- Front Top Temperature `FrontTopHum` DECIMAL(20,5) NULL COMMENT 'Front Top Humidity', -- Front Top Humidity `FrontMidTemp` DECIMAL(20,5) NULL COMMENT 'Front Mid Temperature', -- Front Mid Temperature `FrontMidHum` DECIMAL(20,5) NULL COMMENT 'Front Mid Humidity', -- Front Mid Humidity `FrontBottomTemp` DECIMAL(20,5) NULL COMMENT 'Front Bottom Temperature', -- Front Bottom Temperature `FrontBottomHum` DECIMAL(20,5) NULL COMMENT 'Front Bottom Humidity', -- Front Bottom Humidity `BackTopTemp` DECIMAL(20,5) NULL COMMENT 'Back Top Temperature', -- Back Top Temperature `BackTopHum` DECIMAL(20,5) NULL COMMENT 'Back Top Humidity', -- Back Top Humidity `BackMidTemp` DECIMAL(20,5) NULL COMMENT 'Back Mid Temperature', -- Back Mid Temperature `BackMidHum` DECIMAL(20,5) NULL COMMENT 'Back Mid Humidity', -- Back Mid Humidity `BackBottomTemp` DECIMAL(20,5) NULL COMMENT 'Back Bottom Temperature', -- Back Bottom Temperature `BackBottomHum` DECIMAL(20,5) NULL COMMENT 'Back Bottom Humidity', -- Back Bottom Humidity `FrontAveTemp` DECIMAL(20,5) NULL COMMENT 'Front Average Temperature', -- Front Average Temperature `FrontAveHum` DECIMAL(20,5) NULL COMMENT 'Front Average Humidity', -- Front Average Humidity `BackAveTemp` DECIMAL(20,5) NULL COMMENT 'Back Average Temperature', -- Back Average Temperature `BackAveHum` DECIMAL(20,5) NULL COMMENT 'Back Average Humidity', -- Back Average Humidity `Date` DATETIME NOT NULL COMMENT 'Data Store Time' -- Data Store Time ) COMMENT 'PDU Unit'; -- UPS Status Value CREATE TABLE `tb_ups_statusvalue` ( `UpsID` VARCHAR(20) NOT NULL COMMENT 'UPS ID', -- UPS ID `UPSRatedCapacity` DECIMAL(5,2) NULL COMMENT 'Modular UPS Rated Capacity', -- Modular UPS Rated Capacity `inRating` DECIMAL(5,2) NULL COMMENT 'In Rating', -- In Rating `inRatingLineVoltage` DECIMAL(5,2) NULL COMMENT 'In Rating Line Voltage', -- In Rating Line Voltage `outRating` DECIMAL(5,2) NULL COMMENT 'Out Rating', -- Out Rating `outRatingLineVoltage` DECIMAL(5,2) NULL COMMENT 'Out Rating Line Voltage', -- Out Rating Line Voltage `inVoltageR` DECIMAL(5,2) NULL COMMENT 'In Voltage R', -- In Voltage R `inVoltageS` DECIMAL(5,2) NULL COMMENT 'In Voltage S', -- In Voltage S `inVoltageT` DECIMAL(5,2) NULL COMMENT 'In Voltage T', -- In Voltage T `inCurrentR` DECIMAL(5,2) NULL COMMENT 'In Voltage R', -- In Voltage R `inCurrentS` DECIMAL(5,2) NULL COMMENT 'In Voltage S', -- In Voltage S `inCurrentT` DECIMAL(5,2) NULL COMMENT 'In Voltage T', -- In Voltage T `inFrequency` DECIMAL(5,2) NULL COMMENT 'In Frequency', -- In Frequency `outVoltageR` DECIMAL(5,2) NULL COMMENT 'Out Voltage R', -- Out Voltage R `outVoltageS` DECIMAL(5,2) NULL COMMENT 'Out Voltage S', -- Out Voltage S `outVoltageT` DECIMAL(5,2) NULL COMMENT 'Out Voltage T', -- Out Voltage T `outCurrentR` DECIMAL(5,2) NULL COMMENT 'Out Current R', -- Out Current R `outCurrentS` DECIMAL(5,2) NULL COMMENT 'Out Current S', -- Out Current S `outCurrentT` DECIMAL(5,2) NULL COMMENT 'Out Current T', -- Out Current T `outFrequency` DECIMAL(5,2) NULL COMMENT 'Out Frequency', -- Out Frequency `inApparentElectricPower` DECIMAL(5,2) NULL COMMENT 'In Apparent Electric Power', -- In Apparent Electric Power `inActivePower` DECIMAL(5,2) NULL COMMENT 'In Active Power', -- In Active Power `inReactivePower` DECIMAL(5,2) NULL COMMENT 'In Reactive Power', -- In Reactive Power `outApparentElectricPower` DECIMAL(5,2) NULL COMMENT 'Out Apparent Electric Power', -- Out Apparent Electric Power `outActivePower` DECIMAL(5,2) NULL COMMENT 'Out Active Power', -- Out Active Power `outReactivePower` DECIMAL(5,2) NULL COMMENT 'Out Reactive Power', -- Out Reactive Power `efficiency` DECIMAL(5,2) NULL COMMENT 'Efficiency', -- Efficiency `inEfficiency` DECIMAL(5,2) NULL COMMENT 'In Efficiency', -- In Efficiency `alarmDo` VARCHAR(1) NULL COMMENT 'Warning 1: Alarm, 0:Normal', -- Warning 1: Alarm, 0:Normal `alarmCode` VARCHAR(10) NULL COMMENT 'Alarm Code', -- Alarm Code `Date` DATETIME NULL COMMENT 'Data Store Time' -- Data Store Time ) COMMENT 'UPS Status Value'; -- PDU Unit2 CREATE TABLE `tb_pdu_unitstatus2` ( `PduID` VARCHAR(20) NOT NULL COMMENT 'PDU ID', -- PDU ID `SocketStatus8` VARCHAR(1) NULL COMMENT 'Socket # Status 1', -- Channel Status #8 `SocketPower8` DECIMAL(20,5) NULL COMMENT 'Socket # Power 1', -- Channel Power #8 `SocketStatus9` VARCHAR(1) NULL COMMENT 'Socket # Status 2', -- Channel Status #9 `SocketPower9` DECIMAL(20,5) NULL COMMENT 'Socket # Power 2', -- Channel Power #9 `SocketStatus10` VARCHAR(1) NULL COMMENT 'Socket # Status 3', -- Channel Status #10 `SocketPower10` DECIMAL(20,5) NULL COMMENT 'Socket # Power 3', -- Channel Power #10 `SocketStatus11` VARCHAR(1) NULL COMMENT 'Socket # Status 4', -- Channel Status #11 `SocketPower11` DECIMAL(20,5) NULL COMMENT 'Socket # Power 4', -- Channel Power #11 `SocketStatus12` VARCHAR(1) NULL COMMENT 'Socket # Status 5', -- Channel Status #12 `SocketPower12` DECIMAL(20,5) NULL COMMENT 'Socket # Power 5', -- Channel Power #12 `SocketStatus13` VARCHAR(1) NULL COMMENT 'Socket # Status 6', -- Channel Status #13 `SocketPower13` DECIMAL(20,5) NULL COMMENT 'Socket # Power 6', -- Channel Power #13 `SocketStatus14` VARCHAR(1) NULL COMMENT 'Socket # Status 7', -- Channel Status #14 `SocketPower14` DECIMAL(20,5) NULL COMMENT 'Socket # Power 7', -- Channel Power #14 `Date` DATETIME NULL COMMENT 'Data Store Time' -- Data Store Time ) COMMENT 'PDU Unit2'; -- UPS CREATE TABLE `tb_ups` ( `UpsID` VARCHAR(20) NOT NULL COMMENT 'UPS ID', -- UPS ID `IPAddr` VARCHAR(20) NULL COMMENT 'IP Address' -- IP Address ) COMMENT 'UPS'; -- CRAC CREATE TABLE `tb_crac` ( `CracID` VARCHAR(20) NOT NULL COMMENT 'CRAC ID', -- CRAC ID `IPAddr` VARCHAR(20) NULL COMMENT 'IP Address' -- IP Address ) COMMENT 'CRAC'; -- PDU Unit CREATE TABLE `tb_pdu_unitstatus1` ( `PduID` VARCHAR(20) NOT NULL COMMENT 'PDU ID', -- PDU ID `SocketStatus1` VARCHAR(1) NULL COMMENT 'Socket # Status 1', -- Channel Status #1 `SocketPower1` DECIMAL(20,5) NULL COMMENT 'Socket # Power 1', -- Channel Power #1 `SocketStatus2` VARCHAR(1) NULL COMMENT 'Socket # Status 2', -- Channel Status #2 `SocketPower2` DECIMAL(20,5) NULL COMMENT 'Socket # Power 2', -- Channel Power #2 `SocketStatus3` VARCHAR(1) NULL COMMENT 'Socket # Status 3', -- Channel Status #3 `SocketPower3` DECIMAL(20,5) NULL COMMENT 'Socket # Power 3', -- Channel Power #3 `SocketStatus4` VARCHAR(1) NULL COMMENT 'Socket # Status 4', -- Channel Status #4 `SocketPower4` DECIMAL(20,5) NULL COMMENT 'Socket # Power 4', -- Channel Power #4 `SocketStatus5` VARCHAR(1) NULL COMMENT 'Socket # Status 5', -- Channel Status #5 `SocketPower5` DECIMAL(20,5) NULL COMMENT 'Socket # Power 5', -- Channel Power #5 `SocketStatus6` VARCHAR(1) NULL COMMENT 'Socket # Status 6', -- Channel Status #6 `SocketPower6` DECIMAL(20,5) NULL COMMENT 'Socket # Power 6', -- Channel Power #6 `SocketStatus7` VARCHAR(1) NULL COMMENT 'Socket # Status 7', -- Channel Status #7 `SocketPower7` DECIMAL(20,5) NULL COMMENT 'Socket # Power 7', -- Channel Power #7 `Date` DATETIME NULL COMMENT 'Data Store Time' -- Data Store Time ) COMMENT 'PDU Unit'; USE mysql; CREATE user mdc; SET password for 'mdc' = password('mdc'); GRANT ALL PRIVILEGES ON mdc.* to 'mdc'@'%'; FLUSH PRIVILEGES;
데이터 입력
INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.1.1", "PDU" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.1.2", "PDU" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.1.3", "PDU" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.1.4", "PDU" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.1.5", "PDU" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.1.6", "PDU" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.1.7", "PDU" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.1.8", "PDU" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.1.9", "PDU" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.1.10", "PDU" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.1.11", "PDU" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.1.12", "PDU" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.1.13", "PDU" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.1.14", "PDU" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.1.15", "PDU" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.1.16", "PDU" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.1.17", "PDU" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.1.18", "PDU" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.1.19", "PDU" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.1.20", "PDU" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.2.1", "UPS" ); INSERT INTO tb_pod_devices ( IPAddr, DeviceType ) VALUES ( "10.0.3.1", "CRAC" );
INSERT INTO tb_pdu ( PduID, IPAddr ) VALUES ( "PDU0001", "10.0.1.1" ); INSERT INTO tb_pdu ( PduID, IPAddr ) VALUES ( "PDU0002", "10.0.1.2" ); INSERT INTO tb_pdu ( PduID, IPAddr ) VALUES ( "PDU0003", "10.0.1.3" ); INSERT INTO tb_pdu ( PduID, IPAddr ) VALUES ( "PDU0004", "10.0.1.4" ); INSERT INTO tb_pdu ( PduID, IPAddr ) VALUES ( "PDU0005", "10.0.1.5" ); INSERT INTO tb_pdu ( PduID, IPAddr ) VALUES ( "PDU0006", "10.0.1.6" ); INSERT INTO tb_pdu ( PduID, IPAddr ) VALUES ( "PDU0007", "10.0.1.7" ); INSERT INTO tb_pdu ( PduID, IPAddr ) VALUES ( "PDU0008", "10.0.1.8" ); INSERT INTO tb_pdu ( PduID, IPAddr ) VALUES ( "PDU0009", "10.0.1.9" ); INSERT INTO tb_pdu ( PduID, IPAddr ) VALUES ( "PDU0010", "10.0.1.10" ); INSERT INTO tb_pdu ( PduID, IPAddr ) VALUES ( "PDU0011", "10.0.1.11" ); INSERT INTO tb_pdu ( PduID, IPAddr ) VALUES ( "PDU0012", "10.0.1.12" ); INSERT INTO tb_pdu ( PduID, IPAddr ) VALUES ( "PDU0013", "10.0.1.13" ); INSERT INTO tb_pdu ( PduID, IPAddr ) VALUES ( "PDU0014", "10.0.1.14" ); INSERT INTO tb_pdu ( PduID, IPAddr ) VALUES ( "PDU0015", "10.0.1.15" ); INSERT INTO tb_pdu ( PduID, IPAddr ) VALUES ( "PDU0016", "10.0.1.16" ); INSERT INTO tb_pdu ( PduID, IPAddr ) VALUES ( "PDU0017", "10.0.1.17" ); INSERT INTO tb_pdu ( PduID, IPAddr ) VALUES ( "PDU0018", "10.0.1.18" ); INSERT INTO tb_pdu ( PduID, IPAddr ) VALUES ( "PDU0019", "10.0.1.19" ); INSERT INTO tb_pdu ( PduID, IPAddr ) VALUES ( "PDU0020", "10.0.1.20" );
INSERT INTO tb_ups ( UpsID, IPAddr) VALUES ( "UPS0001", "10.0.2.1" );
INSERT INTO tb_crac ( CracID, IPAddr) VALUES ( "CRC0001", "10.0.3.1" );