server1/SQL/CreateTable-GBase.sqlbook

-- SQLBook: Code

# GBase创建 tACACompareItems
CREATE TABLE tACACompareItems
(
    ItemID SERIAL NOT NULL,
    ItemName VARCHAR(64) NOT NULL,
    ItemEnable BOOLEAN NOT NULL,
    RoadCount INTEGER DEFAULT 0,
    SilentEnable BOOLEAN NOT NULL,
    SilentThreshold INTEGER DEFAULT 0,
    SilentDuration INTEGER DEFAULT 0,
    SilentSensitivity INTEGER DEFAULT 0,
    OverloadEnable BOOLEAN NOT NULL,
    OverloadThreshold INTEGER DEFAULT 0,
    OverloadDuration INTEGER DEFAULT 0,
    OverloadSensitivity INTEGER DEFAULT 0,
    PhaseEnable BOOLEAN NOT NULL,
    PhaseThreshold FLOAT DEFAULT 0,
    PhaseDuration INTEGER DEFAULT 0,
    PhaseSensitivity INTEGER DEFAULT 0,
    PRIMARY KEY (ItemID)
);

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 SERIAL NOT NULL,
    RoadNum INTEGER NOT NULL,
    RoadName VARCHAR(64) NOT NULL,
    RoadRecordEnable BOOLEAN NOT NULL,
    SoundCardName VARCHAR(64),
    SoundCardPCMName VARCHAR(64),
    ChannelID INTEGER,
    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 tACADetectPeriod
(
    ItemID INTEGER NOT NULL,
    IsDetect BOOLEAN NOT NULL, -- 是否检测
    WeekType INTEGER NOT NULL,
    CDate VARCHAR(32),
    TimeStart VARCHAR(32) NOT NULL,
    TimeEnd VARCHAR(32) NOT NULL,
    ApplySlient BOOLEAN NOT NULL,  -- 是否应用静音
    ApplyOverload BOOLEAN NOT NULL, -- 是否应用超载
    ApplyPhase BOOLEAN NOT NULL,   -- 是否应用反相
    ApplyNoise BOOLEAN NOT NULL,   -- 是否应用噪音
    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
#创建系统配置表格

CREATE TABLE tACASystemConfig (
	PKID SERIAL NOT NULL,
	ConfigKey VARCHAR(64) NOT NULL UNIQUE,
	ConfigValue VARCHAR(4096) 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 tACAAlarmInfo
(
    PKID SERIAL NOT NULL,
    ItemID INTEGER NOT NULL,
    ItemName VARCHAR(64) NOT NULL,
    AlarmType INTEGER NOT NULL,             -- 报警类型，1-静音，2-过载，3-反相，4-不一致，5-噪音
    SoundCardName VARCHAR(64),              -- 声卡名称
    SoundCardPCMName VARCHAR(64) NOT NULL,  -- 声卡PCM通道名称
    CompareRoadNum INTEGER NOT NULL,        -- 对比通道编号
    CompareRoadName VARCHAR(64),            -- 对比通道名称
    CompareRoadType INTEGER,                -- 通道类型：1、主输出，2、空收，3、主输出空收
    AlarmStartTime DATETIME YEAR TO FRACTION(5), -- 报警开始时间 (格式：YYYY-MM-DD HH:MM:SS)
    AlarmEndTime DATETIME YEAR TO FRACTION(5), -- 报警结束时间 (格式：YYYY-MM-DD HH:MM:SS)
    AlarmDuration INTEGER,                  -- 报警持续时间（秒数）
    AlarmFilePath VARCHAR(256),             -- 报警录音文件路径
    FileAlarmStartPos INTEGER,              -- 在报警录音文件中报警开始的时间
    MainRoadPKID INTEGER,                   -- 主通道的PKID
    FileState INTEGER DEFAULT 0,            -- 报警文件状态，1-录音中，2-已录音，3-已删除
    CONSTRAINT tACAAlarmInfo PRIMARY KEY (PKID)
)
-- 表注释
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 SERIAL NOT NULL,
    ItemID INTEGER NOT NULL,                            -- 对比项ID
    ItemName VARCHAR(64) NOT NULL,                      -- 对比项名称
    ItemRoadNum INTEGER NOT NULL,                       -- 通道编号
    ItemRoadName VARCHAR(64) NOT NULL,                  -- 通道名称
    SoundCardName VARCHAR(64) ,                         -- 声卡名称
    SoundCardPCMName VARCHAR(64) NOT NULL,              -- 声卡PCM通道名称
    FileStartTime DATETIME YEAR TO FRACTION(5) NOT NULL, -- 录音开始时间 (格式：YYYY-MM-DD HH:MM:SS)
    FileEndTime DATETIME YEAR TO FRACTION(5) NOT NULL,   -- 录音结束时间 (格式: YYYY-MM-DD HH:MM:SS)
    FileDuration INTEGER NOT NULL,                      -- 录音文件持续时间（秒数）
    FilePath VARCHAR(256) NOT NULL,                     -- 录音文件路径
    FileState INTEGER DEFAULT 0,                        -- 录音文件状态，0-未知状态，1-正在录音，2-录音完成，3-文件已删除
    PRIMARY KEY (PKID)
);

-- 表注释
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 INTEGER,                   -- 声卡编号，在系统中的编号，这个不是必须的，有些PCM通道是虚拟的，获取不到属于哪个声卡
    SoundCardRoadNum INTEGER                -- 声卡通道编号，同上
);

-- 表注释
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 '声卡通道编号';