server1/SQL/ACASetting.sqlbook

-- SQLBook: Code
-- Active: 1751960213665@@192.1.2.61@5236@EQM_CESHI

#存储对比项的表格 tACACompareItems
#对比项的对比通道表 tACACompareItemRoad

#创建 tACACompareItems
-- 创建表
CREATE TABLE tACACompareItems
(
    ItemID INT PRIMARY KEY,
    ItemName VARCHAR(64) NOT NULL,
    ItemEnable BIT NOT NULL DEFAULT 1,
    RoadCount INT DEFAULT 0,
    SilentEnable BIT NOT NULL DEFAULT 0,
    SilentThreshold INT DEFAULT 0,
    SilentDuration INT DEFAULT 0,
    SilentSensitivity INT DEFAULT 0,
    OverloadEnable BIT NOT NULL DEFAULT 0,
    OverloadThreshold INT DEFAULT 0,
    OverloadDuration INT DEFAULT 0,
    OverloadSensitivity INT DEFAULT 0,
    PhaseEnable BIT NOT NULL DEFAULT 0,
    PhaseThreshold DOUBLE DEFAULT 0,
    PhaseDuration INT DEFAULT 0,
    PhaseSensitivity INT DEFAULT 0
);
-- 表注释
COMMENT ON TABLE tACACompareItems IS 'ACA对比项表';
-- 字段注释
COMMENT ON COLUMN tACACompareItems.ItemID IS '对比项ID';
COMMENT ON COLUMN tACACompareItems.ItemName IS '对比项名称';
COMMENT ON COLUMN tACACompareItems.ItemEnable IS '对比项是否启用';
COMMENT ON COLUMN tACACompareItems.RoadCount IS '对比通道数';
COMMENT ON COLUMN tACACompareItems.SilentEnable IS '是否启用静音';
COMMENT ON COLUMN tACACompareItems.SilentThreshold IS '静音阈值';
COMMENT ON COLUMN tACACompareItems.SilentDuration IS '静音持续时间';
COMMENT ON COLUMN tACACompareItems.SilentSensitivity IS '静音灵敏度';
COMMENT ON COLUMN tACACompareItems.OverloadEnable IS '是否启用过载';
COMMENT ON COLUMN tACACompareItems.OverloadThreshold IS '过载阈值';
COMMENT ON COLUMN tACACompareItems.OverloadDuration IS '过载持续时间';
COMMENT ON COLUMN tACACompareItems.OverloadSensitivity IS '过载灵敏度';
COMMENT ON COLUMN tACACompareItems.PhaseEnable IS '是否启用反相';
COMMENT ON COLUMN tACACompareItems.PhaseThreshold IS '反相阈值';
COMMENT ON COLUMN tACACompareItems.PhaseDuration IS '反相持续时间';
COMMENT ON COLUMN tACACompareItems.PhaseSensitivity IS '反相灵敏度';
-- 创建索引
-- CREATE INDEX idx_tACACompareItems_ItemName ON tACACompareItems (ItemName);

-- SQLBook: Code
#删除 tACACompareItems
DROP TABLE IF EXISTS "EQM_CESHI".tACACompareItems;
-- SQLBook: Code

#创建 tACACompareItemRoad
#这里设置了级联删除，当 tACACompareItems 表中的某个对比项被删除时，tACACompareItemRoad 中对应的通道也会被删除。
CREATE TABLE tACACompareItemRoad
(
    ItemID INT NOT NULL,
    RoadNum INT NOT NULL,
    RoadName VARCHAR(64) NOT NULL,
    RoadRecordEnable BIT NOT NULL DEFAULT 1,
    SoundCardNum INT NOT NULL DEFAULT 0,
    SoundCardID VARCHAR(64),
    SoundCardName VARCHAR(64),
    SoundCardRoadNum INT NOT NULL DEFAULT 0,
    ChannelID INT,
    ChannelName VARCHAR(64),
    PRIMARY KEY (ItemID, RoadNum),
    FOREIGN KEY (ItemID) REFERENCES tACACompareItems(ItemID)
        ON DELETE CASCADE
);

-- 表注释
COMMENT ON TABLE tACACompareItemRoad IS '对比项通道表';
-- 字段注释
COMMENT ON COLUMN tACACompareItemRoad.ItemID IS '对比项ID';
COMMENT ON COLUMN tACACompareItemRoad.RoadNum IS '通道编号，1是主通道，其他对比通道依次向后排';
COMMENT ON COLUMN tACACompareItemRoad.RoadName IS '通道名称';
COMMENT ON COLUMN tACACompareItemRoad.RoadRecordEnable IS '通道录音是否启用';
COMMENT ON COLUMN tACACompareItemRoad.SoundCardNum IS '声卡编号，在系统中的编号';
COMMENT ON COLUMN tACACompareItemRoad.SoundCardID IS '声卡ID，可以使用声卡ID来打开声卡';
COMMENT ON COLUMN tACACompareItemRoad.SoundCardName IS '声卡名称';
COMMENT ON COLUMN tACACompareItemRoad.SoundCardRoadNum IS '声卡通道编号，使用声卡编号和声卡通道编号开始录音';
COMMENT ON COLUMN tACACompareItemRoad.ChannelID IS '频道ID';
COMMENT ON COLUMN tACACompareItemRoad.ChannelName IS '频道名称';

-- SQLBook: Code
#创建ACA的系统配置表，使用Key获取存储的值，值内容是一个JOSN字符串
CREATE TABLE tACASystemConfig
(
    PKID INT PRIMARY KEY AUTO_INCREMENT,
    ConfigKey VARCHAR(64) NOT NULL UNIQUE,
    ConfigValue TEXT NOT NULL,
    UpdateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ConfigDesc VARCHAR(256)
);
CREATE OR REPLACE TRIGGER trg_update_time
BEFORE UPDATE ON tACASystemConfig
FOR EACH ROW
BEGIN
    :NEW.UpdateTime := CURRENT_TIMESTAMP;
END;
-- 表注释
COMMENT ON TABLE tACASystemConfig IS 'ACA系统配置表';
-- 字段注释
COMMENT ON COLUMN tACASystemConfig.PKID IS '主键ID';
COMMENT ON COLUMN tACASystemConfig.ConfigKey IS '配置键';
COMMENT ON COLUMN tACASystemConfig.ConfigValue IS '配置值，存储为JSON字符串';
COMMENT ON COLUMN tACASystemConfig.UpdateTime IS '配置更新时间';
COMMENT ON COLUMN tACASystemConfig.ConfigDesc IS '配置描述';
-- SQLBook: Code
#创建检测计划表格
CREATE TABLE tACADetectPeriod
(
    ItemID INT NOT NULL,
    IsDetect BIT NOT NULL DEFAULT 1, -- 是否检测
    WeekType INT NOT NULL,
    CDate VARCHAR(32),
    TimeStart VARCHAR(32) NOT NULL,
    TimeEnd VARCHAR(32) NOT NULL,
    ApplySlient BIT NOT NULL DEFAULT 0,  -- 是否应用静音
    ApplyOverload BIT NOT NULL DEFAULT 0, -- 是否应用超载
    ApplyPhase BIT NOT NULL DEFAULT 0,   -- 是否应用反相
    ApplyNoise BIT NOT NULL DEFAULT 0,   -- 是否应用噪音
    FOREIGN KEY (ItemID) REFERENCES tACACompareItems(ItemID)
        ON DELETE CASCADE
);
-- 表注释
COMMENT ON TABLE tACADetectPeriod IS '检测计划表';
-- 字段注释
COMMENT ON COLUMN tACADetectPeriod.ItemID IS '对比项ID';
COMMENT ON COLUMN tACADetectPeriod.IsDetect IS '是否检测，1表示检测日期，0表示非检测日期';
COMMENT ON COLUMN tACADetectPeriod.WeekType IS '检测计划的星期，1-7表示周一到周日，8表示特殊日期，也就是非检测日';
COMMENT ON COLUMN tACADetectPeriod.CDate IS '检测计划的日期';
COMMENT ON COLUMN tACADetectPeriod.TimeStart IS '检测计划的开始时间';
COMMENT ON COLUMN tACADetectPeriod.TimeEnd IS '检测计划的结束时间';
COMMENT ON COLUMN tACADetectPeriod.ApplySlient IS '是否应用静音检测';
COMMENT ON COLUMN tACADetectPeriod.ApplyOverload IS '是否应用超载检测';
COMMENT ON COLUMN tACADetectPeriod.ApplyPhase IS '是否应用反相检测';
COMMENT ON COLUMN tACADetectPeriod.ApplyNoise IS '是否应用噪音检测';
-- 创建索引
CREATE INDEX idx_tACADetectPeriod_ItemID ON tACADetectPeriod (ItemID);
-- SQLBook: Code
-- Active: 1752920752747@@192.1.2.61@5236@EQM_CESHI
#创建报警记录表
CREATE TABLE tACAAlarmInfo
(
    PKID INT PRIMARY KEY AUTO_INCREMENT,
    ItemID INT NOT NULL,
    ItemName VARCHAR(64) NOT NULL,
    AlarmType INT NOT NULL, -- 报警类型，1-静音，2-过载，3-反相，4-噪音
    SoundCardNum VARCHAR(64) NOT NULL, -- 声卡编号（在系统中的编号）
    SoundCardName VARCHAR(64), -- 声卡名称
    SoundCardRoadNum INT NOT NULL, -- 声卡通道编号
    CompareRoadNum INT NOT NULL, -- 对比通道编号
    CompareRoadName VARCHAR(64), -- 对比通道名称
    CompareRoadType INT, -- 通道类型：1、主输出，2、空收，3、主输出空收
    AlarmStartTime VARCHAR(32), -- 报警开始时间 (格式：YYYY-MM-DD HH:MM:SS)
    AlarmEndTime VARCHAR(32), -- 报警结束时间 (格式：YYYY-MM-DD HH:MM:SS)
    AlarmDuration INT, -- 报警持续时间（秒数）
    AlarmFilePath VARCHAR(256), -- 报警录音文件路径
    FileAlarmStartPos int -- 在报警录音文件中报警开始的时间
    MainRoadPKID INT, -- 主通道的PKID
    FOREIGN KEY (ItemID) REFERENCES tACACompareItems(ItemID)
)

-- 表注释
COMMENT ON TABLE tACAAlarmInfo IS 'ACA报警记录表';
-- 字段注释
COMMENT ON COLUMN tACAAlarmInfo.PKID IS '主键ID';
COMMENT ON COLUMN tACAAlarmInfo.ItemID IS '对比项ID';
COMMENT ON COLUMN tACAAlarmInfo.ItemName IS '对比项名称';
COMMENT ON COLUMN tACAAlarmInfo.AlarmType IS '报警类型，1-静音，2-过载，3-反相，4-噪音';
COMMENT ON COLUMN tACAAlarmInfo.SoundCardNum IS '声卡编号（在系统中的编号）';
COMMENT ON COLUMN tACAAlarmInfo.SoundCardName IS '声卡名称';
COMMENT ON COLUMN tACAAlarmInfo.SoundCardRoadNum IS '声卡通道编号';
COMMENT ON COLUMN tACAAlarmInfo.CompareRoadNum IS '对比通道编号';
COMMENT ON COLUMN tACAAlarmInfo.CompareRoadName IS '对比通道名称';
COMMENT ON COLUMN tACAAlarmInfo.CompareRoadType IS '通道类型：1、主输出，2、空收，3、主输出空收';
COMMENT ON COLUMN tACAAlarmInfo.AlarmStartTime IS '报警开始时间 (格式：YYYY-MM-DD HH:MM:SS)';
COMMENT ON COLUMN tACAAlarmInfo.AlarmEndTime IS '报警结束时间 (格式：YYYY-MM-DD HH:MM:SS)';
COMMENT ON COLUMN tACAAlarmInfo.AlarmDuration IS '报警持续时间（秒数）';
COMMENT ON COLUMN tACAAlarmInfo.AlarmFilePath IS '报警录音文件路径';
COMMENT ON COLUMN tACAAlarmInfo.FileAlarmStartPos IS '在报警录音文件中报警开始的时间';
COMMENT ON COLUMN tACAAlarmInfo.MainRoadPKID IS '对比项主通道报警信息的PKID';
-- 创建索引
CREATE INDEX idx_tACAAlarmInfo_ItemID ON tACAAlarmInfo (ItemID);
CREATE INDEX idx_tACAAlarmInfo_AlarmType ON tACAAlarmInfo (AlarmType);
CREATE INDEX idx_tACAAlarmInfo_SoundCardNum ON tACAAlarmInfo (SoundCardNum);
CREATE INDEX idx_tACAAlarmInfo_SoundCardRoadNum ON tACAAlarmInfo (SoundCardRoadNum);
CREATE INDEX idx_tACAAlarmInfo_CompareRoadNum ON tACAAlarmInfo (CompareRoadNum);
CREATE INDEX idx_tACAAlarmInfo_AlarmStartTime ON tACAAlarmInfo (AlarmStartTime);
CREATE INDEX idx_tACAAlarmInfo_AlarmEndTime ON tACAAlarmInfo (AlarmEndTime);