You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
217 lines
26 KiB
217 lines
26 KiB
3 months ago
|
-- 2024-07-31 （未执行）
|
||
|
|
alter table project_info add longitude numeric(10,2) null;
|
||
|
|
exec sp_addextendedproperty N'MS_Description', N'经度', N'schema', N'dbo', N'table', N'project_info', N'column', N'longitude';
|
||
|
|
|
||
|
|
alter table project_info add latitude numeric(10,2) null;
|
||
|
|
exec sp_addextendedproperty N'MS_Description', N'纬度', N'schema', N'dbo', N'table', N'project_info', N'column', N'latitude';
|
||
|
|
|
||
|
|
alter table project_info add create_time datetime null;
|
||
|
|
exec sp_addextendedproperty N'MS_Description', N'创建时间', N'schema', N'dbo', N'table', N'project_info', N'column', N'create_time';
|
||
|
|
|
||
|
|
alter table project_info add pic_content text null;
|
||
|
|
exec sp_addextendedproperty N'MS_Description', N'图片内容', N'schema', N'dbo', N'table', N'project_info', N'column', N'pic_content';
|
||
|
|
|
||
|
|
-- 2024-08-13 创建字典表数据
|
||
|
|
CREATE TABLE sys_dict (
|
||
|
|
id bigint NOT NULL,
|
||
|
|
value nvarchar(100) COLLATE Chinese_PRC_CI_AS NOT NULL,
|
||
|
|
label nvarchar(100) COLLATE Chinese_PRC_CI_AS NOT NULL,
|
||
|
|
[type] nvarchar(100) COLLATE Chinese_PRC_CI_AS NOT NULL,
|
||
|
|
description nvarchar(100) COLLATE Chinese_PRC_CI_AS NOT NULL,
|
||
|
|
sort decimal(10,0) NOT NULL,
|
||
|
|
create_by nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
|
||
|
|
create_time datetime2 NULL,
|
||
|
|
last_update_by nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
|
||
|
|
last_update_time datetime2 NULL,
|
||
|
|
remarks nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL,
|
||
|
|
del_flag tinyint NULL,
|
||
|
|
status bit null,
|
||
|
|
parent_id bigint null
|
||
|
|
CONSTRAINT PK__sys_dict__3213E83F527BBC48 PRIMARY KEY (id)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Extended properties
|
||
|
|
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'字典表', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'sys_dict';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编号', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'sys_dict', @level2type=N'Column', @level2name=N'id';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据值', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'sys_dict', @level2type=N'Column', @level2name=N'value';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'标签名', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'sys_dict', @level2type=N'Column', @level2name=N'label';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'类型', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'sys_dict', @level2type=N'Column', @level2name=N'type';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'描述', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'sys_dict', @level2type=N'Column', @level2name=N'description';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'排序（升序）', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'sys_dict', @level2type=N'Column', @level2name=N'sort';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'sys_dict', @level2type=N'Column', @level2name=N'create_by';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'sys_dict', @level2type=N'Column', @level2name=N'create_time';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新人', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'sys_dict', @level2type=N'Column', @level2name=N'last_update_by';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新时间', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'sys_dict', @level2type=N'Column', @level2name=N'last_update_time';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注信息', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'sys_dict', @level2type=N'Column', @level2name=N'remarks';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否删除 -1：已删除 0：正常', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'sys_dict', @level2type=N'Column', @level2name=N'del_flag';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'状态（0正常 1停用）', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'sys_dict', @level2type=N'Column', @level2name=N'status';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'父id：0最高级', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'sys_dict', @level2type=N'Column', @level2name=N'parent_id';
|
||
|
|
|
||
|
|
-- 2024-08-22
|
||
|
|
-- 资产设备管理
|
||
|
|
create table devices_manage
|
||
|
|
(
|
||
|
|
id bigint identity primary key not null, -- id
|
||
|
|
device_name nvarchar(100) null, -- 设备名称
|
||
|
|
device_brand nvarchar(100) null, -- 设备品牌
|
||
|
|
em_brand nvarchar(100) null, -- 机电品牌
|
||
|
|
rated_input_power numeric(24, 2) null, -- 额定输入功率
|
||
|
|
rated_refrigeration_capacity numeric(24, 2) null, -- 额定制冷量
|
||
|
|
rated_cop numeric(24, 2) null, -- 额定COP
|
||
|
|
rated_flow numeric(24, 2) null, -- 额定流量
|
||
|
|
production_time datetime null, -- 生产日期
|
||
|
|
create_time datetime null, -- 创建时间
|
||
|
|
update_time datetime null, -- 更新时间
|
||
|
|
create_by nvarchar(50) null, -- 创建人
|
||
|
|
is_frequency bit null, -- 是否变频
|
||
|
|
device_type bigint null, -- 设备类型id（字典配置）
|
||
|
|
system_id bigint null, -- 项目类型id
|
||
|
|
remark nvarchar(255) null, -- 备注
|
||
|
|
grade int null -- 保留
|
||
|
|
)
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'资产设备管理', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编号', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'id';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'设备名称', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'device_name';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'设备品牌', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'device_brand';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'机电品牌', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'em_brand';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'额定输入功率', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'rated_input_power';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'额定制冷量', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'rated_refrigeration_capacity';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'额定COP', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'rated_cop';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'额定流量', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'rated_flow';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生产日期', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'production_time';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'create_time';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新时间', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'update_time';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'create_by';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否变频', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'is_frequency';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'设备类型id（字典配置）', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'device_type';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'项目类型id', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'system_id';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'remark';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据标识', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'grade';
|
||
|
|
|
||
|
|
-- 仪表管理
|
||
|
|
create table meter_manage (
|
||
|
|
id bigint identity primary key not null, -- 编号
|
||
|
|
mt_type bigint null, -- 仪表类型（从字典值获取）
|
||
|
|
mt_name nvarchar(100) null, -- 仪表名称
|
||
|
|
other_name nvarchar(100) null, -- 仪表别名
|
||
|
|
mt_brand nvarchar(100) null, -- 仪表品牌
|
||
|
|
mt_num nvarchar(20) null, -- 仪表表面号
|
||
|
|
mt_code nvarchar(20) null, -- 仪表采集码
|
||
|
|
register_addr nvarchar(20), -- 寄存器地址
|
||
|
|
func_code nvarchar(20) null, -- 功能码
|
||
|
|
identify_code nvarchar(20) null, -- 仪表识别码
|
||
|
|
mt_caliber_pulse nvarchar(20) null, -- 仪表口径或者脉冲常数
|
||
|
|
mt_range numeric(24,3) null, -- 仪表范围
|
||
|
|
mt_ratio int null, -- 仪表倍率
|
||
|
|
mt_init_value numeric(24,3) null, -- 初始值
|
||
|
|
digits int null, -- 小数位数
|
||
|
|
data_type int null, -- 数据类型16位、32位、64位
|
||
|
|
cur_value numeric(24,3) null, -- 当前值
|
||
|
|
cur_time datetime null, -- 当前值时间
|
||
|
|
mt_is_sum bit null, -- 是否总表
|
||
|
|
create_time datetime null, -- 创建时间
|
||
|
|
update_time datetime null, -- 更新时间
|
||
|
|
unit nvarchar(20) null, -- 单位
|
||
|
|
sort bigint null, -- 排序
|
||
|
|
data_com nvarchar(100) null, -- 通信端口
|
||
|
|
device_id bigint null, -- 对应device_manage的id
|
||
|
|
gateway_id bigint null, -- 对应网关管理表gateway_manage的id
|
||
|
|
param_id bigint null, -- 对应参数id
|
||
|
|
protocol_type bigint null, -- 协议类型(字典表)
|
||
|
|
communication_type int NULL, -- 通讯方式
|
||
|
|
remark nvarchar(255) null, -- 备注
|
||
|
|
system_id bigint null, -- 项目类型id
|
||
|
|
grade int null -- 保留
|
||
|
|
)
|
||
|
|
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'仪表管理', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'meter_manage';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编号', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'meter_manage', @level2type=N'Column', @level2name=N'id';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'仪表类型（从字典值获取）', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'meter_manage', @level2type=N'Column', @level2name=N'mt_type_id';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'仪表名称', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'meter_manage', @level2type=N'Column', @level2name=N'mt_name';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'仪表别名', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'meter_manage', @level2type=N'Column', @level2name=N'other_name';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'仪表品牌', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'meter_manage', @level2type=N'Column', @level2name=N'mt_brand';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'仪表表面号', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'meter_manage', @level2type=N'Column', @level2name=N'mt_num';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'仪表采集码', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'meter_manage', @level2type=N'Column', @level2name=N'mt_code';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'寄存器地址', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'meter_manage', @level2type=N'Column', @level2name=N'register_addr';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'功能码', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'meter_manage', @level2type=N'Column', @level2name=N'func_code';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'仪表识别码', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'meter_manage', @level2type=N'Column', @level2name=N'identify_code';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'仪表口径或者脉冲常数', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'meter_manage', @level2type=N'Column', @level2name=N'mt_caliber_pulse';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'仪表范围', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'meter_manage', @level2type=N'Column', @level2name=N'mt_range';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'仪表倍率', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'meter_manage', @level2type=N'Column', @level2name=N'mt_ratio';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'初始值', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'meter_manage', @level2type=N'Column', @level2name=N'mt_init_value';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'小数位数', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'meter_manage', @level2type=N'Column', @level2name=N'digits';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据类型16位、32位、64位', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'data_type';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'当前值', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'cur_value';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'当前值时间', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'cur_time';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否总表', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'mt_is_sum';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'create_time';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新时间', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'update_time';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'单位', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'unit';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'排序', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'sort';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'对应device_manage的id', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'device_id';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'对应网关管理表gateway_manage的id', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'gateway_id';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'对应参数id', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'param_id';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'协议类型(字典表)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'protocol_id';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'通讯方式', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'communication_type';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'remark';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'项目类型ID', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'system_id';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'保留', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'grade';
|
||
|
|
|
||
|
|
|
||
|
|
-- 网关管理表
|
||
|
|
CREATE TABLE gateway_manage (
|
||
|
|
id bigint identity(1,1) NOT NULL,
|
||
|
|
gw_name varchar(100) COLLATE Chinese_PRC_CI_AS NULL,
|
||
|
|
gw_ip varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
|
||
|
|
gw_addr varchar(100) COLLATE Chinese_PRC_CI_AS NULL,
|
||
|
|
port int NULL,
|
||
|
|
collection_loop int NULL,
|
||
|
|
create_time datetime NULL,
|
||
|
|
update_time datetime NULL,
|
||
|
|
connect_time datetime NULL,
|
||
|
|
internet_card varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
|
||
|
|
operator_type int NULL,
|
||
|
|
remark varchar(100) COLLATE Chinese_PRC_CI_AS NULL,
|
||
|
|
communication_type int NULL,
|
||
|
|
grade int NULL,
|
||
|
|
system_id bigint null, -- 项目类型id
|
||
|
|
CONSTRAINT pk_gateway_manage PRIMARY KEY (id)
|
||
|
|
);
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'网关管理', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'gateway_manage';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编号', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'gateway_manage', @level2type=N'Column', @level2name=N'id';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'网关名称', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'gateway_manage', @level2type=N'Column', @level2name=N'gw_name';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'网关IP地址', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'gateway_manage', @level2type=N'Column', @level2name=N'gw_ip';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'网关对应的编号地址', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'gateway_manage', @level2type=N'Column', @level2name=N'gw_addr';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'网关端口', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'gateway_manage', @level2type=N'Column', @level2name=N'port';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'采集周期', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'gateway_manage', @level2type=N'Column', @level2name=N'collection_loop';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'gateway_manage', @level2type=N'Column', @level2name=N'create_time';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新时间', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'gateway_manage', @level2type=N'Column', @level2name=N'update_time';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'连接时间', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'gateway_manage', @level2type=N'Column', @level2name=N'connect_time';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'卡号', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'gateway_manage', @level2type=N'Column', @level2name=N'internet_card';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'运营商类型 0:中国移动 1:中国联通 2:中国电信', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'gateway_manage', @level2type=N'Column', @level2name=N'operator_type';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'gateway_manage', @level2type=N'Column', @level2name=N'remark';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'通信类型(字典表)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'gateway_manage', @level2type=N'Column', @level2name=N'communication_type_id';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'gateway_manage', @level2type=N'Column', @level2name=N'grade';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'项目类型id', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'gateway_manage', @level2type=N'Column', @level2name=N'system_id';
|
||
|
|
|
||
|
|
-- 设备采集参数表
|
||
|
|
create table device_params(
|
||
|
|
id bigint primary key identity(1,1) not null, -- 编号
|
||
|
|
mt_type int not null, -- 仪表类型（从字典表中拿）
|
||
|
|
baud_rate int not null, -- 波特率
|
||
|
|
data_bit int not null, -- 数据位
|
||
|
|
stop_bit int not null, -- 停止位
|
||
|
|
parity nvarchar(10) not null, -- 校验位
|
||
|
|
remark varchar(100) null -- 备注
|
||
|
|
)
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'设备采集参数表', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'device_params';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编号', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'device_params', @level2type=N'Column', @level2name=N'id';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'仪表类型(从字典中获取)', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'device_params', @level2type=N'Column', @level2name=N'mt_type';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'波特率', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'device_params', @level2type=N'Column', @level2name=N'baud_rate';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据位', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'device_params', @level2type=N'Column', @level2name=N'data_bit';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'停止位', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'device_params', @level2type=N'Column', @level2name=N'stop_bit';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'校验位', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'device_params', @level2type=N'Column', @level2name=N'parity';
|
||
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'device_params', @level2type=N'Column', @level2name=N'remark';
|
||
|
|
|
||
|
|
-- 2024-08-22
|