lijianfu
/
mh_esi
1
0
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
节能岛公众号
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.
15 Commits
3 Branches
0 Tags
1.7 MiB
 
Branch: dev
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'dev'
${ noResults }
mh_esi/2024新增脚本.sql

236 lines
28 KiB
Raw Permalink Blame History

-- 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
project_id bigint null, -- 项目类型id
remark nvarchar(255) 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'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'系统id', @level0type=N'Schema', @level0name=N'dbo', @level1type=N'Table', @level1name=N'devices_manage', @level2type=N'Column', @level2name=N'project_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
project_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'project_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'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
project_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';
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'project_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
-- 2028-09-02
ALTER TABLE devices_manage ADD factory_barcode varchar(100) NULL;
EXEC sys.sp_addextendedproperty 'MS_Description', N'出厂条码', 'schema', N'dbo', 'table', N'devices_manage', 'column', N'factory_barcode';
ALTER TABLE devices_manage ADD imu_code varchar(100) NULL;
EXEC sys.sp_addextendedproperty 'MS_Description', N'IMU编码', 'schema', N'dbo', 'table', N'devices_manage', 'column', N'imu_code';
ALTER TABLE meter_manage ADD register_size int DEFAULT 1 NULL;
EXEC sys.sp_addextendedproperty 'MS_Description', N'寄存器大小', 'schema', N'dbo', 'table', N'meter_manage', 'column', N'register_size';
ALTER TABLE meter_manage ADD is_use bit DEFAULT 1 NULL;
EXEC sys.sp_addextendedproperty 'MS_Description', N'当前采集点位是否启用', 'schema', N'dbo', 'table', N'meter_manage', 'column', N'is_use';