forecast/gateway

-- 通信模块表 DROP TABLE IF EXISTS dev_transmit;

CREATE TABLE dev_transmit ( id bigserial NOT NULL, name varchar(50) NOT NULL, transmit_class varchar(100) NOT NULL DEFAULT '', config_example varchar(500) NOT NULL DEFAULT '', PRIMARY KEY (id), CONSTRAINT uq_dev_transmit_1 UNIQUE (name) );

COMMENT ON TABLE dev_transmit IS '通信模块'; COMMENT ON COLUMN dev_transmit.id IS '主键'; COMMENT ON COLUMN dev_transmit.name IS '名称'; COMMENT ON COLUMN dev_transmit.transmit_class IS '类全名'; COMMENT ON COLUMN dev_transmit.config_example IS '配置示例';

-- 协议模块表 DROP TABLE IF EXISTS dev_protocol;

CREATE TABLE dev_protocol ( id bigserial NOT NULL, name varchar(50) NOT NULL, protocol_class varchar(100) NOT NULL, config_example varchar(500) NOT NULL DEFAULT '', PRIMARY KEY (id), CONSTRAINT uq_dev_protocol_1 UNIQUE (name) );

COMMENT ON TABLE dev_protocol IS '协议模块'; COMMENT ON COLUMN dev_protocol.id IS '主键'; COMMENT ON COLUMN dev_protocol.name IS '名称'; COMMENT ON COLUMN dev_protocol.protocol_class IS '全类名'; COMMENT ON COLUMN dev_protocol.config_example IS '配置示例';

-- 设备通道表 DROP TABLE IF EXISTS dev_channel;

CREATE TABLE dev_channel ( id bigserial NOT NULL, code varchar(15) NOT NULL DEFAULT '', name varchar(50) NOT NULL, disabled char(1) NOT NULL DEFAULT '0', dev_transmit_id bigint NOT NULL, transmit_config varchar(500) NOT NULL DEFAULT '', dev_protocol_id bigint NOT NULL, protocol_config varchar(500) NOT NULL DEFAULT '', remark varchar(200) NOT NULL DEFAULT '', create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, create_by varchar(25) NOT NULL DEFAULT '', update_by varchar(25) NOT NULL DEFAULT '', PRIMARY KEY (id) );

COMMENT ON TABLE dev_channel IS '设备通道表'; COMMENT ON COLUMN dev_channel.id IS '主键'; COMMENT ON COLUMN dev_channel.code IS '编号'; COMMENT ON COLUMN dev_channel.name IS '名称'; COMMENT ON COLUMN dev_channel.disabled IS '停用状态'; COMMENT ON COLUMN dev_channel.dev_transmit_id IS '通信模块'; COMMENT ON COLUMN dev_channel.transmit_config IS '通信配置'; COMMENT ON COLUMN dev_channel.dev_protocol_id IS '协议模块'; COMMENT ON COLUMN dev_channel.protocol_config IS '协议配置'; COMMENT ON COLUMN dev_channel.remark IS '备注'; COMMENT ON COLUMN dev_channel.create_time IS '创建时间'; COMMENT ON COLUMN dev_channel.update_time IS '修改时间'; COMMENT ON COLUMN dev_channel.create_by IS '创建者'; COMMENT ON COLUMN dev_channel.update_by IS '更新者';

-- 本地气象数据 drop table if exists data_weather_local; CREATE TABLE data_weather_local ( tm TIMESTAMPTZ NOT NULL, device_id bigint NOT NULL, horizontal_irradiance DOUBLE PRECISION, inclined_irradiance DOUBLE PRECISION, temperature DOUBLE PRECISION, pressure DOUBLE PRECISION, humidity DOUBLE PRECISION, wind_speed DOUBLE PRECISION, wind_direction DOUBLE PRECISION )WITH ( tsdb.hypertable, tsdb.partition_column='tm', tsdb.segmentby = 'device_id', tsdb.chunk_interval='1 month', tsdb.orderby = 'tm DESC' );

COMMENT ON COLUMN data_weather_local.tm IS '时间戳（带时区）'; COMMENT ON COLUMN data_weather_local.device_id IS '设备ID'; COMMENT ON COLUMN data_weather_local.horizontal_irradiance IS '水平面总辐照度，单位 W/m²'; COMMENT ON COLUMN data_weather_local.inclined_irradiance IS '斜面辐照度，单位 W/m²'; COMMENT ON COLUMN data_weather_local.temperature IS '温度，单位 ℃'; COMMENT ON COLUMN data_weather_local.pressure IS '气压，单位 hPa'; COMMENT ON COLUMN data_weather_local.humidity IS '湿度，单位 %'; COMMENT ON COLUMN data_weather_local.wind_speed IS '风速，单位 m/s'; COMMENT ON COLUMN data_weather_local.wind_direction IS '风向，北向为0，顺时针，单位 °（0~360）';

-- 场站电气采样数据 DROP TABLE IF EXISTS data_station; CREATE TABLE data_station ( station_id bigint NOT NULL, tm TIMESTAMPTZ NOT NULL, actual_power DOUBLE PRECISION, theoretical_power DOUBLE PRECISION, curtailment_power DOUBLE PRECISION, availability DOUBLE PRECISION, device_status SMALLINT, grid_status SMALLINT ) with ( tsdb.hypertable, tsdb.partition_column='tm', tsdb.chunk_interval=''1 month'', tsdb.orderby = 'tm DESC' ); COMMENT ON COLUMN data_station.station_id IS '光伏场站ID'; COMMENT ON COLUMN data_station.tm IS '时间戳（带时区）'; COMMENT ON COLUMN data_station.actual_power IS '实际发电功率（kW）'; COMMENT ON COLUMN data_station.theoretical_power IS '理论发电功率（kW）'; COMMENT ON COLUMN data_station.curtailment_power IS '限电功率（kW）'; COMMENT ON COLUMN data_station.device_status IS '设备状态（0=正常，1=停机，2=限电，3=检修，4=故障,只要一个处于异常就设为某个异常）'; COMMENT ON COLUMN data_station.grid_status IS '并网状态（1=并网，0=离网）'; COMMENT ON COLUMN data_station.availability IS '可用率（%）';