tba-gx-guochan/sql/init-schema.sql

-- ============================================================
-- 太湖流域水环境信息共享平台 - 达梦数据库DDL脚本
-- 数据库: 达梦DM8
-- 创建时间: 2026-07-12
-- ============================================================

-- -----------------------------------------------------------
-- 1. 数据交换业务域表
-- -----------------------------------------------------------
DROP TABLE IF EXISTS gx_exchange_biz;
CREATE TABLE gx_exchange_biz (
    id          BIGINT IDENTITY(1,1) PRIMARY KEY,
    biz_code    VARCHAR(50)  NOT NULL,
    biz_name    VARCHAR(200) NOT NULL,
    biz_type    VARCHAR(50),
    biz_unit    VARCHAR(200),
    biz_impl_type VARCHAR(50),
    biz_impl_url VARCHAR(500),
    biz_state   CHAR(1) DEFAULT '0',
    data_freq   VARCHAR(50),
    create_by   VARCHAR(64)  DEFAULT '',
    create_time TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    update_by   VARCHAR(64)  DEFAULT '',
    update_time TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    remark      VARCHAR(500),
    del_flag    CHAR(1)      DEFAULT '0'
);
COMMENT ON TABLE gx_exchange_biz IS '数据交换业务域表';
COMMENT ON COLUMN gx_exchange_biz.biz_code IS '业务编码(如TBA_SW_1H2H)';
COMMENT ON COLUMN gx_exchange_biz.biz_name IS '业务名称';
COMMENT ON COLUMN gx_exchange_biz.biz_type IS '业务类型(水位/水质/工程)';
COMMENT ON COLUMN gx_exchange_biz.biz_unit IS '提供单位';
COMMENT ON COLUMN gx_exchange_biz.biz_impl_type IS '实现方式(API/文件)';
COMMENT ON COLUMN gx_exchange_biz.biz_impl_url IS '接口地址';
COMMENT ON COLUMN gx_exchange_biz.biz_state IS '状态(0正常 1停用)';
COMMENT ON COLUMN gx_exchange_biz.data_freq IS '数据频率(实时/小时/日)';
CREATE UNIQUE INDEX idx_biz_code ON gx_exchange_biz(biz_code);

-- -----------------------------------------------------------
-- 2. 数据交换站点表
-- -----------------------------------------------------------
DROP TABLE IF EXISTS gx_exchange_station;
CREATE TABLE gx_exchange_station (
    id          BIGINT IDENTITY(1,1) PRIMARY KEY,
    biz_code    VARCHAR(50)  NOT NULL,
    stcd        VARCHAR(50)  NOT NULL,
    stnm        VARCHAR(200),
    dbcd        VARCHAR(50),
    stlc        VARCHAR(200),
    lgtd        DECIMAL(12,6),
    lttd        DECIMAL(12,6),
    rvnm        VARCHAR(100),
    hnnm        VARCHAR(100),
    bsnm        VARCHAR(100),
    admauth     VARCHAR(200),
    state       CHAR(1)      DEFAULT '0',
    flag_1h3h   CHAR(1)      DEFAULT '0',
    gcdl        VARCHAR(50),
    note        VARCHAR(500),
    create_by   VARCHAR(64)  DEFAULT '',
    create_time TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    update_by   VARCHAR(64)  DEFAULT '',
    update_time TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    remark      VARCHAR(500),
    del_flag    CHAR(1)      DEFAULT '0'
);
COMMENT ON TABLE gx_exchange_station IS '数据交换站点表';
COMMENT ON COLUMN gx_exchange_station.biz_code IS '业务编码';
COMMENT ON COLUMN gx_exchange_station.stcd IS '站码';
COMMENT ON COLUMN gx_exchange_station.stnm IS '站名';
COMMENT ON COLUMN gx_exchange_station.dbcd IS '数据库编码';
COMMENT ON COLUMN gx_exchange_station.lgtd IS '经度';
COMMENT ON COLUMN gx_exchange_station.lttd IS '纬度';
COMMENT ON COLUMN gx_exchange_station.rvnm IS '河流名称';
COMMENT ON COLUMN gx_exchange_station.hnnm IS '湖名称';
COMMENT ON COLUMN gx_exchange_station.bsnm IS '流域名称';
COMMENT ON COLUMN gx_exchange_station.flag_1h3h IS '一三类标识';
CREATE INDEX idx_station_biz ON gx_exchange_station(biz_code);
CREATE INDEX idx_station_stcd ON gx_exchange_station(stcd);

-- -----------------------------------------------------------
-- 3. 数据共享申请表
-- -----------------------------------------------------------
DROP TABLE IF EXISTS gx_exchange_apply;
CREATE TABLE gx_exchange_apply (
    id            BIGINT IDENTITY(1,1) PRIMARY KEY,
    unit_name     VARCHAR(200) NOT NULL,
    user_id       BIGINT,
    biz_code      VARCHAR(50)  NOT NULL,
    apply_time    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    apply_expire  TIMESTAMP,
    apply_state   CHAR(1)      DEFAULT '0',
    approve_time  TIMESTAMP,
    approve_user  VARCHAR(64),
    approve_remark VARCHAR(500),
    create_by     VARCHAR(64)  DEFAULT '',
    create_time   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    update_by     VARCHAR(64)  DEFAULT '',
    update_time   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    remark        VARCHAR(500),
    del_flag      CHAR(1)      DEFAULT '0'
);
COMMENT ON TABLE gx_exchange_apply IS '数据共享申请表';
COMMENT ON COLUMN gx_exchange_apply.apply_state IS '审批状态(0待审 1通过 2驳回)';
CREATE INDEX idx_apply_biz ON gx_exchange_apply(biz_code);
CREATE INDEX idx_apply_state ON gx_exchange_apply(apply_state);

-- -----------------------------------------------------------
-- 4. 数据交换参数表
-- -----------------------------------------------------------
DROP TABLE IF EXISTS gx_exchange_param;
CREATE TABLE gx_exchange_param (
    id           BIGINT IDENTITY(1,1) PRIMARY KEY,
    biz_code     VARCHAR(50)  NOT NULL,
    param_code   VARCHAR(50)  NOT NULL,
    param_name   VARCHAR(200),
    param_type   VARCHAR(50),
    param_value  VARCHAR(500),
    param_state  CHAR(1)      DEFAULT '0',
    create_by    VARCHAR(64)  DEFAULT '',
    create_time  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    update_by    VARCHAR(64)  DEFAULT '',
    update_time  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    remark       VARCHAR(500),
    del_flag     CHAR(1)      DEFAULT '0'
);
COMMENT ON TABLE gx_exchange_param IS '数据交换参数表';
CREATE INDEX idx_param_biz ON gx_exchange_param(biz_code);

-- -----------------------------------------------------------
-- 5. 数据交换定时器表
-- -----------------------------------------------------------
DROP TABLE IF EXISTS gx_exchange_timer;
CREATE TABLE gx_exchange_timer (
    id          BIGINT IDENTITY(1,1) PRIMARY KEY,
    biz_code    VARCHAR(50)  NOT NULL,
    start_time  TIMESTAMP,
    end_time    TIMESTAMP,
    end_state   CHAR(1)      DEFAULT '0',
    run_count   INT          DEFAULT 0,
    create_by   VARCHAR(64)  DEFAULT '',
    create_time TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    update_by   VARCHAR(64)  DEFAULT '',
    update_time TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    remark      VARCHAR(500),
    del_flag    CHAR(1)      DEFAULT '0'
);
COMMENT ON TABLE gx_exchange_timer IS '数据交换定时器表';
CREATE UNIQUE INDEX idx_timer_biz ON gx_exchange_timer(biz_code);

-- -----------------------------------------------------------
-- 6. 数据交换日志表
-- -----------------------------------------------------------
DROP TABLE IF EXISTS gx_exchange_log;
CREATE TABLE gx_exchange_log (
    id          BIGINT IDENTITY(1,1) PRIMARY KEY,
    biz_code    VARCHAR(50)  NOT NULL,
    biz_name    VARCHAR(200),
    log_time    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    content     CLOB,
    dnum        INT          DEFAULT 0,
    create_by   VARCHAR(64)  DEFAULT '',
    create_time TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    del_flag    CHAR(1)      DEFAULT '0'
);
COMMENT ON TABLE gx_exchange_log IS '数据交换日志表';
CREATE INDEX idx_log_biz ON gx_exchange_log(biz_code);
CREATE INDEX idx_log_time ON gx_exchange_log(log_time);

-- -----------------------------------------------------------
-- 7. 数据质量评估表
-- -----------------------------------------------------------
DROP TABLE IF EXISTS gx_exchange_quality;
CREATE TABLE gx_exchange_quality (
    id             BIGINT IDENTITY(1,1) PRIMARY KEY,
    biz_code       VARCHAR(50)  NOT NULL,
    stcd           VARCHAR(50)  NOT NULL,
    analysis_date  DATE,
    type           VARCHAR(50),
    value          DECIMAL(10,4),
    cnt            INT          DEFAULT 0,
    description    VARCHAR(500),
    starttime      TIMESTAMP,
    endtime        TIMESTAMP,
    create_by      VARCHAR(64)  DEFAULT '',
    create_time    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    update_by      VARCHAR(64)  DEFAULT '',
    update_time    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    del_flag       CHAR(1)      DEFAULT '0'
);
COMMENT ON TABLE gx_exchange_quality IS '数据质量评估表';
CREATE INDEX idx_quality_biz ON gx_exchange_quality(biz_code);
CREATE INDEX idx_quality_stcd ON gx_exchange_quality(stcd);

-- -----------------------------------------------------------
-- 8. 数据交换单位表
-- -----------------------------------------------------------
DROP TABLE IF EXISTS gx_exchange_unit;
CREATE TABLE gx_exchange_unit (
    id          BIGINT IDENTITY(1,1) PRIMARY KEY,
    unit_code   VARCHAR(50)  NOT NULL,
    unit_name   VARCHAR(200) NOT NULL,
    type        VARCHAR(50),
    addr        VARCHAR(500),
    linkman     VARCHAR(100),
    tele        VARCHAR(50),
    mail        VARCHAR(100),
    fax         VARCHAR(50),
    url         VARCHAR(500),
    note        CLOB,
    create_by   VARCHAR(64)  DEFAULT '',
    create_time TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    update_by   VARCHAR(64)  DEFAULT '',
    update_time TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    remark      VARCHAR(500),
    del_flag    CHAR(1)      DEFAULT '0'
);
COMMENT ON TABLE gx_exchange_unit IS '数据交换单位表';
CREATE UNIQUE INDEX idx_unit_code ON gx_exchange_unit(unit_code);

-- -----------------------------------------------------------
-- 9. 调度指令水位表
-- -----------------------------------------------------------
DROP TABLE IF EXISTS gx_ddzl_wl;
CREATE TABLE gx_ddzl_wl (
    id          BIGINT IDENTITY(1,1) PRIMARY KEY,
    biz_code    VARCHAR(50),
    stcd        VARCHAR(50),
    spt         TIMESTAMP,
    upz         DECIMAL(10,4),
    dwz         DECIMAL(10,4),
    q           DECIMAL(12,4),
    ysl         DECIMAL(10,4),
    psl         DECIMAL(10,4),
    ddzl        VARCHAR(200),
    yxzt        VARCHAR(50),
    create_by   VARCHAR(64)  DEFAULT '',
    create_time TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    update_by   VARCHAR(64)  DEFAULT '',
    update_time TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE gx_ddzl_wl IS '调度指令水位数据表';

-- -----------------------------------------------------------
-- 10. 调度指令水质表
-- -----------------------------------------------------------
DROP TABLE IF EXISTS gx_ddzl_sz;
CREATE TABLE gx_ddzl_sz (
    id          BIGINT IDENTITY(1,1) PRIMARY KEY,
    biz_code    VARCHAR(50),
    stcd        VARCHAR(50),
    spt         TIMESTAMP,
    wt          DECIMAL(10,4),
    codmn       DECIMAL(10,4),
    ph          DECIMAL(10,4),
    cond        DECIMAL(10,4),
    dox         DECIMAL(10,4),
    nh3n        DECIMAL(10,4),
    tn          DECIMAL(10,4),
    tp          DECIMAL(10,4),
    turb        DECIMAL(10,4),
    create_by   VARCHAR(64)  DEFAULT '',
    create_time TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    update_by   VARCHAR(64)  DEFAULT '',
    update_time TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE gx_ddzl_sz IS '调度指令水质数据表';

-- -----------------------------------------------------------
-- 11. 引江济太站点表
-- -----------------------------------------------------------
DROP TABLE IF EXISTS YJJT_STATION;
CREATE TABLE YJJT_STATION (
    STCD        VARCHAR(50)  NOT NULL,
    STNM        VARCHAR(200),
    STTP        VARCHAR(50),
    RVNM        VARCHAR(100),
    HNNM        VARCHAR(100),
    BSNM        VARCHAR(100),
    STLC        VARCHAR(200),
    LGTD        DECIMAL(12,6),
    LTTD        DECIMAL(12,6),
    ADDVCD      VARCHAR(50),
    JCDW        VARCHAR(200),
    JCPC        VARCHAR(100),
    JCXM        VARCHAR(200),
    FLAG        CHAR(1),
    DBCD        VARCHAR(50),
    YEAR        VARCHAR(10)  NOT NULL,
    PROV        VARCHAR(50),
    CREATE_BY   VARCHAR(64)  DEFAULT '',
    CREATE_TIME TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY   VARCHAR(64)  DEFAULT '',
    UPDATE_TIME TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    REMARK      VARCHAR(500),
    DEL_FLAG    CHAR(1)      DEFAULT '0',
    PRIMARY KEY (STCD, YEAR)
);
COMMENT ON TABLE YJJT_STATION IS '引江济太站点表';
COMMENT ON COLUMN YJJT_STATION.STCD IS '测站编码';
COMMENT ON COLUMN YJJT_STATION.STNM IS '测站名称';
COMMENT ON COLUMN YJJT_STATION.STTP IS '站点类型';
COMMENT ON COLUMN YJJT_STATION.RVNM IS '河流名称';
COMMENT ON COLUMN YJJT_STATION.HNNM IS '湖泊名称';
COMMENT ON COLUMN YJJT_STATION.BSNM IS '水系名称';
COMMENT ON COLUMN YJJT_STATION.STLC IS '站点位置';
COMMENT ON COLUMN YJJT_STATION.LGTD IS '经度';
COMMENT ON COLUMN YJJT_STATION.LTTD IS '纬度';
COMMENT ON COLUMN YJJT_STATION.ADDVCD IS '数据来源';
COMMENT ON COLUMN YJJT_STATION.JCDW IS '监测单位';
COMMENT ON COLUMN YJJT_STATION.JCPC IS '监测频次';
COMMENT ON COLUMN YJJT_STATION.JCXM IS '监测项目';
COMMENT ON COLUMN YJJT_STATION.FLAG IS '标志';
COMMENT ON COLUMN YJJT_STATION.DBCD IS '数据库编码';
COMMENT ON COLUMN YJJT_STATION.YEAR IS '年份';
COMMENT ON COLUMN YJJT_STATION.PROV IS '所属省份';

-- -----------------------------------------------------------
-- 12. 引江济太水位数据表
-- -----------------------------------------------------------
DROP TABLE IF EXISTS YJJT_SL_DATA;
CREATE TABLE YJJT_SL_DATA (
    ID          BIGINT IDENTITY(1,1) PRIMARY KEY,
    STCD        VARCHAR(50),
    STNM        VARCHAR(200),
    RVNM        VARCHAR(100),
    ADDVCD      VARCHAR(50),
    SPT         TIMESTAMP,
    Z           DECIMAL(10,4),
    Z_J         DECIMAL(10,4),
    Q           DECIMAL(12,4),
    LIUXIANG    VARCHAR(50),
    NT          VARCHAR(50),
    CREATE_BY   VARCHAR(64)  DEFAULT '',
    CREATE_TIME TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY   VARCHAR(64)  DEFAULT '',
    UPDATE_TIME TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE YJJT_SL_DATA IS '引江济太水位流量数据表';

-- -----------------------------------------------------------
-- 13. 引江济太水质数据表
-- -----------------------------------------------------------
DROP TABLE IF EXISTS YJJT_SZ_DATA;
CREATE TABLE YJJT_SZ_DATA (
    ID          BIGINT IDENTITY(1,1) PRIMARY KEY,
    STCD        VARCHAR(50),
    STNM        VARCHAR(200),
    RVNM        VARCHAR(100),
    SPT         TIMESTAMP,
    ADDVCD      VARCHAR(50),
    WT          DECIMAL(10,4),
    PH          DECIMAL(10,4),
    DOX         DECIMAL(10,4),
    CODMMN       DECIMAL(10,4),
    CODCR       DECIMAL(10,4),
    BOD5        DECIMAL(10,4),
    NH3N        DECIMAL(10,4),
    TP          DECIMAL(10,4),
    TN          DECIMAL(10,4),
    CU          DECIMAL(10,4),
    ZN          DECIMAL(10,4),
    CREATE_BY   VARCHAR(64)  DEFAULT '',
    CREATE_TIME TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY   VARCHAR(64)  DEFAULT '',
    UPDATE_TIME TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE YJJT_SZ_DATA IS '引江济太水质数据表';

-- -----------------------------------------------------------
-- 14. 引江济太文件表
-- -----------------------------------------------------------
DROP TABLE IF EXISTS YJJT_FILE;
CREATE TABLE YJJT_FILE (
    ID             BIGINT IDENTITY(1,1) PRIMARY KEY,
    FILE_VIEWNAME  VARCHAR(500),
    FILE_SAVENAME  VARCHAR(500),
    FILE_PATH      VARCHAR(500),
    FILE_SIZE      BIGINT DEFAULT 0,
    FILE_TYPE      VARCHAR(50),
    BIZ_TYPE       VARCHAR(50),
    UPLOAD_BY      VARCHAR(64),
    UPLOAD_TIME    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    STATUS         CHAR(1)      DEFAULT '0',
    REMARK         VARCHAR(500),
    CREATE_BY      VARCHAR(64)  DEFAULT '',
    CREATE_TIME    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY      VARCHAR(64)  DEFAULT '',
    UPDATE_TIME    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE YJJT_FILE IS '引江济太文件表';

-- -----------------------------------------------------------
-- 初始化数据：插入核心业务域
-- -----------------------------------------------------------
INSERT INTO gx_exchange_biz(biz_code, biz_name, biz_type, biz_unit, biz_state, data_freq) VALUES('TBA_SW_1H2H', '太湖局水位数据(小时)', '水位', '太湖流域管理局', '0', '小时');
INSERT INTO gx_exchange_biz(biz_code, biz_name, biz_type, biz_unit, biz_state, data_freq) VALUES('TBA_SZ_1H2H', '太湖局水质数据(小时)', '水质', '太湖流域管理局', '0', '小时');
INSERT INTO gx_exchange_biz(biz_code, biz_name, biz_type, biz_unit, biz_state, data_freq) VALUES('JS_SL_SW', '江苏省水位数据', '水位', '江苏省水利厅', '0', '小时');
INSERT INTO gx_exchange_biz(biz_code, biz_name, biz_type, biz_unit, biz_state, data_freq) VALUES('JS_SL_GCDD', '江苏省工程调度', '调度', '江苏省水利厅', '0', '实时');
INSERT INTO gx_exchange_biz(biz_code, biz_name, biz_type, biz_unit, biz_state, data_freq) VALUES('ZJ_SL_GCDD', '浙江省工程调度', '调度', '浙江省水利厅', '0', '实时');
INSERT INTO gx_exchange_biz(biz_code, biz_name, biz_type, biz_unit, biz_state, data_freq) VALUES('SH_ST_SYD', '上海市水源地数据', '水质', '上海市水务局', '0', '日');
COMMIT;