80 lines
6.1 KiB
SQL
80 lines
6.1 KiB
SQL
-- ============================================================
|
||
-- 物模型数据推送配置 (微信服务号模板消息)
|
||
-- 执行顺序：在主库 fastbee 中执行
|
||
-- ============================================================
|
||
|
||
CREATE TABLE IF NOT EXISTS `iot_things_model_push_config` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`config_name` VARCHAR(128) NOT NULL COMMENT '配置名称',
|
||
`product_id` BIGINT NOT NULL COMMENT '产品ID',
|
||
`device_id` BIGINT DEFAULT NULL COMMENT '设备ID（必须指定具体设备）',
|
||
`serial_number` VARCHAR(64) DEFAULT NULL COMMENT '设备编号',
|
||
`template_id` BIGINT NOT NULL COMMENT '通知模板ID（关联 notify_template.id）',
|
||
`push_interval` INT DEFAULT 60 COMMENT '推送间隔(分钟)，0=仅手动推送',
|
||
`last_push_time` DATETIME DEFAULT NULL COMMENT '最近一次推送时间',
|
||
`status` TINYINT DEFAULT 1 COMMENT '状态：0=停用 1=启用',
|
||
`tenant_id` BIGINT DEFAULT NULL COMMENT '租户ID',
|
||
`tenant_name` VARCHAR(64) DEFAULT NULL COMMENT '租户名称',
|
||
`user_id` BIGINT DEFAULT NULL COMMENT '配置归属用户ID',
|
||
`create_by` VARCHAR(64) DEFAULT NULL COMMENT '创建者',
|
||
`create_time` DATETIME DEFAULT NULL COMMENT '创建时间',
|
||
`update_by` VARCHAR(64) DEFAULT NULL COMMENT '更新者',
|
||
`update_time` DATETIME DEFAULT NULL COMMENT '更新时间',
|
||
`del_flag` CHAR(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_device_id` (`device_id`),
|
||
KEY `idx_template_id` (`template_id`),
|
||
KEY `idx_user_id` (`user_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='物模型数据推送配置';
|
||
|
||
|
||
CREATE TABLE IF NOT EXISTS `iot_push_model_item` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`config_id` BIGINT NOT NULL COMMENT '推送配置ID',
|
||
`model_id` BIGINT DEFAULT NULL COMMENT '物模型ID（iot_things_model.model_id）',
|
||
`identifier` VARCHAR(128) NOT NULL COMMENT '物模型标识符',
|
||
`model_name` VARCHAR(128) DEFAULT NULL COMMENT '物模型名称（冗余展示用）',
|
||
`unit` VARCHAR(32) DEFAULT NULL COMMENT '单位（冗余展示用）',
|
||
`green_min` VARCHAR(64) DEFAULT NULL COMMENT '绿色(正常)下限，NULL表示不限',
|
||
`green_max` VARCHAR(64) DEFAULT NULL COMMENT '绿色(正常)上限，NULL表示不限',
|
||
`yellow_min` VARCHAR(64) DEFAULT NULL COMMENT '黄色(告警)下限，NULL表示不限',
|
||
`yellow_max` VARCHAR(64) DEFAULT NULL COMMENT '黄色(告警)上限，NULL表示不限',
|
||
`sort_order` INT DEFAULT 0 COMMENT '排列顺序',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_config_id` (`config_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='推送物模型项及等级阈值配置';
|
||
|
||
|
||
CREATE TABLE IF NOT EXISTS `iot_push_user` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`config_id` BIGINT NOT NULL COMMENT '推送配置ID',
|
||
`user_id` BIGINT NOT NULL COMMENT '系统用户ID（需已绑定微信公众号）',
|
||
`user_name` VARCHAR(64) DEFAULT NULL COMMENT '用户名（冗余展示用）',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_config_id` (`config_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='推送接收用户';
|
||
|
||
|
||
-- ============================================================
|
||
-- sys_menu 菜单权限（挂在"规则引擎"父菜单 parent_id=3051 下）
|
||
-- ============================================================
|
||
|
||
INSERT INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3430, '数据联动', 3343, 9, 'pushConfig', 'iot/push/index', NULL, 1, 0, 'C', '0', '0', 'iot:push:list', 'notification', 'admin', now(), 'admin', now(), '微信服务号物模型数据推送');
|
||
|
||
INSERT INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3431, '推送配置查询', 3430, 1, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:push:query', '#', 'admin', now(), 'admin', now(), '');
|
||
|
||
INSERT INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3432, '推送配置新增', 3430, 2, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:push:add', '#', 'admin', now(), 'admin', now(), '');
|
||
|
||
INSERT INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3433, '推送配置修改', 3430, 3, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:push:edit', '#', 'admin', now(), 'admin', now(), '');
|
||
|
||
INSERT INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3434, '推送配置删除', 3430, 4, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:push:remove', '#', 'admin', now(), 'admin', now(), '');
|
||
|
||
INSERT INTO `sys_menu` (menu_id, menu_name, parent_id, order_num, `path`, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
VALUES (3435, '手动立即推送', 3430, 5, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:push:push', '#', 'admin', now(), 'admin', now(), '');
|
||
|