yanzhu-saas/yanzhu-module-iot-mysql.sql

CREATE TABLE IF NOT EXISTS `iot_scene_rule` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL DEFAULT '',
    `description` varchar(500) DEFAULT NULL,
    `status` tinyint NOT NULL DEFAULT '0',
    `triggers` text,
    `actions` text,
    `creator` varchar(64) DEFAULT '',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updater` varchar(64) DEFAULT '',
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` tinyint(1) NOT NULL DEFAULT '0',
    `tenant_id` bigint NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) COMMENT='IoT 场景联动规则表';

CREATE TABLE IF NOT EXISTS `iot_product` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL DEFAULT '',
    `product_key` varchar(100) NOT NULL DEFAULT '',
    `protocol_type` tinyint NOT NULL DEFAULT '0',
    `category_id` bigint DEFAULT NULL,
    `description` varchar(500) DEFAULT NULL,
    `data_format` tinyint NOT NULL DEFAULT '0',
    `device_type` tinyint NOT NULL DEFAULT '0',
    `net_type` tinyint NOT NULL DEFAULT '0',
    `validate_type` tinyint NOT NULL DEFAULT '0',
    `status` tinyint NOT NULL DEFAULT '0',
    `creator` varchar(64) DEFAULT '',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updater` varchar(64) DEFAULT '',
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` tinyint(1) NOT NULL DEFAULT '0',
    `tenant_id` bigint NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) COMMENT='IoT 产品表';

CREATE TABLE IF NOT EXISTS `iot_device` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `device_name` varchar(255) NOT NULL DEFAULT '',
    `product_id` bigint NOT NULL,
    `device_key` varchar(100) NOT NULL DEFAULT '',
    `device_secret` varchar(100) NOT NULL DEFAULT '',
    `nickname` varchar(255) DEFAULT NULL,
    `status` tinyint NOT NULL DEFAULT '0',
    `status_last_update_time` timestamp DEFAULT NULL,
    `last_online_time` timestamp DEFAULT NULL,
    `last_offline_time` timestamp DEFAULT NULL,
    `active_time` timestamp DEFAULT NULL,
    `ip` varchar(50) DEFAULT NULL,
    `firmware_version` varchar(50) DEFAULT NULL,
    `device_type` tinyint NOT NULL DEFAULT '0',
    `gateway_id` bigint DEFAULT NULL,
    `sub_device_count` int NOT NULL DEFAULT '0',
    `creator` varchar(64) DEFAULT '',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updater` varchar(64) DEFAULT '',
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` tinyint(1) NOT NULL DEFAULT '0',
    `tenant_id` bigint NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) COMMENT='IoT 设备表';

CREATE TABLE IF NOT EXISTS `iot_thing_model` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `product_id` bigint NOT NULL,
    `identifier` varchar(100) NOT NULL DEFAULT '',
    `name` varchar(255) NOT NULL DEFAULT '',
    `description` varchar(500) DEFAULT NULL,
    `type` tinyint NOT NULL DEFAULT '1',
    `property` text,
    `creator` varchar(64) DEFAULT '',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updater` varchar(64) DEFAULT '',
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` tinyint(1) NOT NULL DEFAULT '0',
    `tenant_id` bigint NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) COMMENT='IoT 物模型表';

CREATE TABLE IF NOT EXISTS `iot_device_data` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `device_id` bigint NOT NULL,
    `product_id` bigint NOT NULL,
    `identifier` varchar(100) NOT NULL DEFAULT '',
    `type` tinyint NOT NULL DEFAULT '1',
    `data` text,
    `ts` bigint NOT NULL DEFAULT '0',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) COMMENT='IoT 设备数据表';

CREATE TABLE IF NOT EXISTS `iot_alert_config` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL DEFAULT '',
    `product_id` bigint NOT NULL,
    `device_id` bigint DEFAULT NULL,
    `rule_id` bigint DEFAULT NULL,
    `status` tinyint NOT NULL DEFAULT '0',
    `creator` varchar(64) DEFAULT '',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updater` varchar(64) DEFAULT '',
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` tinyint(1) NOT NULL DEFAULT '0',
    `tenant_id` bigint NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) COMMENT='IoT 告警配置表';

CREATE TABLE IF NOT EXISTS `iot_alert_record` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `alert_config_id` bigint NOT NULL,
    `alert_name` varchar(255) NOT NULL DEFAULT '',
    `product_id` bigint NOT NULL,
    `device_id` bigint DEFAULT NULL,
    `rule_id` bigint DEFAULT NULL,
    `alert_data` text,
    `alert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deal_status` tinyint NOT NULL DEFAULT '0',
    `deal_time` timestamp DEFAULT NULL,
    `deal_user_id` bigint DEFAULT NULL,
    `deal_remark` varchar(500) DEFAULT NULL,
    `creator` varchar(64) DEFAULT '',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updater` varchar(64) DEFAULT '',
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` tinyint(1) NOT NULL DEFAULT '0',
    `tenant_id` bigint NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) COMMENT='IoT 告警记录表';

CREATE TABLE IF NOT EXISTS `iot_ota_firmware` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL DEFAULT '',
    `product_id` bigint NOT NULL,
    `version` varchar(50) NOT NULL DEFAULT '',
    `description` varchar(500) DEFAULT NULL,
    `file_url` varchar(500) DEFAULT NULL,
    `file_size` bigint NOT NULL DEFAULT '0',
    `status` tinyint NOT NULL DEFAULT '0',
    `creator` varchar(64) DEFAULT '',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updater` varchar(64) DEFAULT '',
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` tinyint(1) NOT NULL DEFAULT '0',
    `tenant_id` bigint NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) COMMENT='IoT OTA 固件表';

CREATE TABLE IF NOT EXISTS `iot_ota_task` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL DEFAULT '',
    `firmware_id` bigint NOT NULL,
    `product_id` bigint NOT NULL,
    `upgrade_type` tinyint NOT NULL DEFAULT '0',
    `status` tinyint NOT NULL DEFAULT '0',
    `creator` varchar(64) DEFAULT '',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updater` varchar(64) DEFAULT '',
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` tinyint(1) NOT NULL DEFAULT '0',
    `tenant_id` bigint NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) COMMENT='IoT OTA 升级任务表';

CREATE TABLE IF NOT EXISTS `iot_ota_record` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `task_id` bigint NOT NULL,
    `firmware_id` bigint NOT NULL,
    `device_id` bigint NOT NULL,
    `status` tinyint NOT NULL DEFAULT '0',
    `progress` int NOT NULL DEFAULT '0',
    `error_msg` varchar(500) DEFAULT NULL,
    `start_time` timestamp DEFAULT NULL,
    `end_time` timestamp DEFAULT NULL,
    `creator` varchar(64) DEFAULT '',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updater` varchar(64) DEFAULT '',
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` tinyint(1) NOT NULL DEFAULT '0',
    `tenant_id` bigint NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) COMMENT='IoT OTA 升级记录表';

-- 额外添加设备分组表
CREATE TABLE IF NOT EXISTS `iot_device_group` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL DEFAULT '',
    `description` varchar(500) DEFAULT NULL,
    `creator` varchar(64) DEFAULT '',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updater` varchar(64) DEFAULT '',
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` tinyint(1) NOT NULL DEFAULT '0',
    `tenant_id` bigint NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) COMMENT='IoT 设备分组表';

-- 额外添加产品分类表
CREATE TABLE IF NOT EXISTS `iot_product_category` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL DEFAULT '',
    `parent_id` bigint DEFAULT NULL,
    `description` varchar(500) DEFAULT NULL,
    `creator` varchar(64) DEFAULT '',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updater` varchar(64) DEFAULT '',
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` tinyint(1) NOT NULL DEFAULT '0',
    `tenant_id` bigint NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) COMMENT='IoT 产品分类表';

-- 额外添加数据规则表
CREATE TABLE IF NOT EXISTS `iot_data_rule` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL DEFAULT '',
    `description` varchar(500) DEFAULT NULL,
    `status` tinyint NOT NULL DEFAULT '0',
    `rule_config` text,
    `creator` varchar(64) DEFAULT '',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updater` varchar(64) DEFAULT '',
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` tinyint(1) NOT NULL DEFAULT '0',
    `tenant_id` bigint NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) COMMENT='IoT 数据规则表';

-- 额外添加数据 sink 表
CREATE TABLE IF NOT EXISTS `iot_data_sink` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL DEFAULT '',
    `type` tinyint NOT NULL DEFAULT '0',
    `config` text,
    `status` tinyint NOT NULL DEFAULT '0',
    `creator` varchar(64) DEFAULT '',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updater` varchar(64) DEFAULT '',
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` tinyint(1) NOT NULL DEFAULT '0',
    `tenant_id` bigint NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) COMMENT='IoT 数据输出表';