server1/SQL/CreateTable-达梦.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
-- Active: 1752718919967@@192.1.2.61@5236@EQM_CESHI
#删除 tACACompareItems
DROP TABLE IF EXISTS "EQM_CESHI".tACACompareItems;

#删除 tACAAlarmInfo
DROP TABLE IF EXISTS "EQM_CESHI".tACAAlarmInfo;

#删除 tACARecordFile
DROP TABLE IF EXISTS "EQM_CESHI".tACARecordFile;
-- 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,
    SoundCardName VARCHAR(64),
    SoundCardPCMName VARCHAR(64) 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.SoundCardName IS '声卡名称';
COMMENT ON COLUMN tACACompareItemRoad.SoundCardPCMName IS '声卡PCM通道名称，使用这个名称打开录音';
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: 1752718919967@@192.1.2.61@5236@EQM_CESHI
-- 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-不一致，5-噪音
    SoundCardName VARCHAR(64),          -- 声卡名称
    SoundCardPCMName VARCHAR(64) NOT NULL, -- 声卡PCM通道名称
    CompareRoadNum INT NOT NULL,        -- 对比通道编号
    CompareRoadName VARCHAR(64),        -- 对比通道名称
    CompareRoadType INT,                -- 通道类型：1、主输出，2、空收，3、主输出空收
    AlarmStartTime TIMESTAMP(6),        -- 报警开始时间 (格式：YYYY-MM-DD HH:MM:SS)
    AlarmEndTime TIMESTAMP(6),          -- 报警结束时间 (格式：YYYY-MM-DD HH:MM:SS)
    AlarmDuration INT,                  -- 报警持续时间（秒数）
    AlarmFilePath VARCHAR(256),         -- 报警录音文件路径
    FileAlarmStartPos int,              -- 在报警录音文件中报警开始的时间
    MainRoadPKID INT,                   -- 主通道的PKID
    FileState INT DEFAULT 0             -- 报警文件状态，1-录音中，2-已录音，3-已删除
    -- 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-不一致，5-噪音';
COMMENT ON COLUMN tACAAlarmInfo.SoundCardName IS '声卡名称';
COMMENT ON COLUMN tACAAlarmInfo.SoundCardPCMName IS '声卡PCM通道名称';
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';
COMMENT ON COLUMN tACAAlarmInfo.FileState IS '报警文件状态，1-录音中，2-已录音，3-已删除';

-- 创建索引
CREATE INDEX idx_tACAAlarmInfo_ItemID ON tACAAlarmInfo (ItemID);
CREATE INDEX idx_tACAAlarmInfo_AlarmType ON tACAAlarmInfo (AlarmType);
CREATE INDEX idx_tACAAlarmInfo_CompareRoadNum ON tACAAlarmInfo (CompareRoadNum);
CREATE INDEX idx_tACAAlarmInfo_AlarmStartTime ON tACAAlarmInfo (AlarmStartTime);
CREATE INDEX idx_tACAAlarmInfo_AlarmEndTime ON tACAAlarmInfo (AlarmEndTime);
-- SQLBook: Code
#创建记录录音文件的表格
CREATE TABLE tACARecordFile
(
    PKID INT PRIMARY KEY AUTO_INCREMENT,
    ItemID INT NOT NULL,                    -- 对比项ID
    ItemName VARCHAR(64) NOT NULL,          -- 对比项名称
    ItemRoadNum INT NOT NULL,               -- 通道编号
    ItemRoadName VARCHAR(64) NOT NULL,      -- 通道名称
    SoundCardName VARCHAR(64) NOT NULL,     -- 声卡名称
    SoundCardPCMName VARCHAR(64) NOT NULL,  -- 声卡PCM通道编号
    FileStartTime TIMESTAMP(6) NOT NULL,     -- 录音开始时间 (格式：YYYY-MM-DD HH:MM:SS)
    FileEndTime TIMESTAMP(6) NOT NULL,       -- 录音结束时间 (格式: YYYY-MM-DD HH:MM:SS)
    FileDuration INT NOT NULL,              -- 录音文件持续时间（秒数）
    FilePath VARCHAR(256) NOT NULL,         -- 录音文件路径
    FileState INT DEFAULT 0                 -- 录音文件状态，0-未知状态，1-正在录音，2-录音完成，3-文件已删除
    -- FOREIGN KEY (ItemID) REFERENCES tACACompareItems(ItemID)
    --     ON DELETE CASCADE
);

-- 表注释
COMMENT ON TABLE tACARecordFile IS 'ACA录音文件记录表';
-- 字段注释
COMMENT ON COLUMN tACARecordFile.PKID IS '主键ID';
COMMENT ON COLUMN tACARecordFile.ItemID IS '对比项ID';
COMMENT ON COLUMN tACARecordFile.ItemName IS '对比项名称';
COMMENT ON COLUMN tACARecordFile.ItemRoadNum IS '通道编号';
COMMENT ON COLUMN tACARecordFile.ItemRoadName IS '通道名称';
COMMENT ON COLUMN tACARecordFile.SoundCardName IS '声卡名称';
COMMENT ON COLUMN tACARecordFile.SoundCardPCMName IS '声卡PCM通道编号';
COMMENT ON COLUMN tACARecordFile.FileStartTime IS '录音开始时间 (格式：YYYY-MM-DD HH:MM:SS)';
COMMENT ON COLUMN tACARecordFile.FileEndTime IS '录音结束时间 (格式: YYYY-MM-DD HH:MM:SS)';
COMMENT ON COLUMN tACARecordFile.FileDuration IS '录音文件持续时间（秒数）';
COMMENT ON COLUMN tACARecordFile.FilePath IS '录音文件路径';
COMMENT ON COLUMN tACARecordFile.FileState IS '录音文件状态，0-未知状态，1-正在录音，2-录音完成，3-文件已删除';
-- 创建索引
CREATE INDEX idx_tACARecordFile_ItemID ON tACARecordFile (ItemID);
CREATE INDEX idx_tACARecordFile_ItemRoadNum ON tACARecordFile (ItemRoadNum);
CREATE INDEX idx_tACARecordFile_FileStartTime ON tACARecordFile (FileStartTime);
CREATE INDEX idx_tACARecordFile_FileEndTime ON tACARecordFile (FileEndTime);
CREATE INDEX idx_tACARecordFile_FileDuration ON tACARecordFile (FileDuration);


-- SQLBook: Code
#创建存储声卡PCM通道的表格
CREATE TABLE tACASoundCardPCMChannels
(
    -- PKID INT PRIMARY KEY AUTO_INCREMENT,
    SoundCardName VARCHAR(64) NOT NULL,   -- 声卡驱动名称，自定义的，用来表示PCM通道的
    PCMName VARCHAR(64) NOT NULL,           -- PCM通道名称
    PCMDesc VARCHAR(256) NOT NULL,          -- PCM通道描述
    IOID VARCHAR(32),                       -- 输入输出
    SoundCardNum INT,                       -- 声卡编号，在系统中的编号，这个不是必须的，有些PCM通道是虚拟的，获取不到属于哪个声卡
    SoundCardRoadNum INT                    -- 声卡通道编号，同上
);

-- 表注释
COMMENT ON TABLE tACASoundCardPCMChannels IS '存储声卡PCM通道信息的表格';
-- 字段注释
-- COMMENT ON COLUMN tACASoundCardPCMChannels.PKID IS '主键ID';
COMMENT ON COLUMN tACASoundCardPCMChannels.SoundCardName IS '声卡名称';
COMMENT ON COLUMN tACASoundCardPCMChannels.PCMName IS 'PCM通道名称';
COMMENT ON COLUMN tACASoundCardPCMChannels.PCMDesc IS 'PCM通道描述';
COMMENT ON COLUMN tACASoundCardPCMChannels.IOID IS '输入输出标识，表示这个PCM通道是输入还是输出';
COMMENT ON COLUMN tACASoundCardPCMChannels.SoundCardNum IS '声卡编号';
COMMENT ON COLUMN tACASoundCardPCMChannels.SoundCardRoadNum IS '声卡通道编号';