由于项目需要我公司需要开发一个物联网项目,之前也没有相应的案例,所以从网上找到开源的thingsboard项目(下文统称tb)。公司之前的项目都是使用mysql数据库,而tb项目使用的关系数据库是postgresql,因此为了更好的处理数据库中的数据领导让我实现使用mysql启动thingsboard,下面开始我曲折的探索过程。
首先是在mysql数据库上建表和插入基础数据,建表语句和插入语句如下:
建表语句:
CREATE TABLE `admin_settings` (
`id` varchar(31) NOT NULL,
`json_value` varchar(5000) DEFAULT NULL,
`key` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `alarm` (
`id` varchar(31) NOT NULL,
`ack_ts` bigint(20) DEFAULT NULL,
`clear_ts` bigint(20) DEFAULT NULL,
`additional_info` varchar(218) DEFAULT NULL,
`end_ts` bigint(20) DEFAULT NULL,
`originator_id` varchar(31) DEFAULT NULL,
`originator_type` int(11) DEFAULT NULL,
`propagate` tinyint(1) DEFAULT NULL,
`severity` varchar(255) DEFAULT NULL,
`start_ts` bigint(20) DEFAULT NULL,
`status` varchar(255) DEFAULT NULL,
`tenant_id` varchar(31) DEFAULT NULL,
`propagate_relation_types` varchar(218) DEFAULT NULL,
`type` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `asset` (
`id` varchar(31) NOT NULL,
`additional_info` varchar(218) DEFAULT NULL,
`customer_id` varchar(31) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`label` varchar(255) DEFAULT NULL,
`search_text` varchar(255) DEFAULT NULL,
`tenant_id` varchar(31) DEFAULT NULL,
`type` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `attribute_kv` (
`entity_type` varchar(255) NOT NULL,
`entity_id` varchar(31) DEFAULT NULL,
`attribute_type` varchar(255) DEFAULT NULL,
`attribute_key` varchar(255) DEFAULT NULL,
`bool_v` tinyint(1) DEFAULT NULL,
`str_v` varchar(255) DEFAULT NULL,
`long_v` bigint(20) DEFAULT NULL,
`dbl_v` double DEFAULT NULL,
`last_update_ts` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `audit_log` (
`id` varchar(31) NOT NULL,
`tenant_id` varchar(31) DEFAULT NULL,
`customer_id` varchar(31) DEFAULT NULL,
`entity_id` varchar(31) DEFAULT NULL,
`entity_type` varchar(255) DEFAULT NULL,
`entity_name` varchar(255) DEFAULT NULL,
`user_id` varchar(31) DEFAULT NULL,
`user_name` varchar(255) DEFAULT NULL,
`action_type` varchar(255) DEFAULT NULL,
`action_data` varchar(255) DEFAULT NULL,
`action_status` varchar(255) DEFAULT NULL,
`action_failure_details` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `component_descriptor` (
`id` varchar(31) NOT NULL,
`actions` varchar(255) DEFAULT NULL,
`clazz` varchar(255) DEFAULT NULL,
`configuration_descriptor` varchar(1000) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`scope` varchar(255) DEFAULT NULL,
`search_text` varchar(255) DEFAULT NULL,
`type` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `customer` (
`id` varchar(31) NOT NULL,
`additional_info` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`address2` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`country` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`phone` varchar(255) DEFAULT NULL,
`search_text` varchar(255) DEFAULT NULL,
`state` varchar(255) DEFAULT NULL,
`tenant_id` varchar(31) DEFAULT NULL,
`title` varchar(255) DEFAULT NULL,
`zip` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `dashboard` (
`id` varchar(31) NOT NULL,
`configuration` varchar(255) DEFAULT NULL,
`assigned_customers` varchar(255) DEFAULT NULL,
`search_text` varchar(255) DEFAULT NULL,
`tenant_id` varchar(31) DEFAULT NULL,
`title` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `device` (
`id` varchar(31) NOT NULL,
`additional_info` varchar(255) DEFAULT NULL,
`customer_id` varchar(31) DEFAULT NULL,
`type` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`label` varchar(255) DEFAULT NULL,
`search_text` varchar(255) DEFAULT NULL,
`tenant_id` varchar(31) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `device_credentials` (
`id` varchar(31) NOT NULL,
`credentials_id` varchar(255) DEFAULT NULL,
`credentials_type` varchar(255) DEFAULT NULL,
`credentials_value` varchar(255) DEFAULT NULL,
`device_id` varchar(31) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `entity_view` (
`id` varchar(31) NOT NULL,
`entity_id` varchar(31) DEFAULT NULL,
`entity_type` varchar(255) DEFAULT NULL,
`tenant_id` varchar(31) DEFAULT NULL,
`customer_id` varchar(31) DEFAULT NULL,
`type` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`keys` varchar(255) DEFAULT NULL,
`start_ts` bigint(20) DEFAULT NULL,
`end_ts` bigint(20) DEFAULT NULL,
`search_text` varchar(255) DEFAULT NULL,
`additional_info` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `event` (
`id` varchar(31) NOT NULL,
`body` varchar(255) DEFAULT NULL,
`entity_id` varchar(31) DEFAULT NULL,
`entity_type` varchar(255) DEFAULT NULL,
`event_type` varchar(255) DEFAULT NULL,
`event_uid` varchar(255) DEFAULT NULL,
`tenant_id` varchar(31) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `relation` (
`from_id` varchar(31) NOT NULL,
`from_type` varchar(255) DEFAULT NULL,
`to_id` varchar(31) DEFAULT NULL,
`to_type` varchar(255) DEFAULT NULL,
`relation_type_group` varchar(255) DEFAULT NULL,
`relation_type` varchar(255) DEFAULT NULL,
`additional_info` varchar(255) DEFAULT NULL,
PRIMARY KEY (`from_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `rule_chain` (
`id` varchar(255) NOT NULL,
`additional_info` varchar(255) DEFAULT NULL,
`configuration` varchar(255) DEFAULT NULL,
`debug_mode` bit(1) DEFAULT NULL,
`first_rule_node_id` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`root` bit(1) DEFAULT NULL,
`search_text` varchar(255) DEFAULT NULL,
`tenant_id` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `rule_node` (
`id` varchar(31) NOT NULL,
`rule_chain_id` varchar(31) DEFAULT NULL,
`additional_info` varchar(255) DEFAULT NULL,
`configuration` varchar(255) DEFAULT NULL,
`type` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`debug_mode` tinyint(1) DEFAULT NULL,
`search_text` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `tb_user` (
`id` varchar(31) NOT NULL,
`additional_info` varchar(255) DEFAULT NULL,
`authority` varchar(255) DEFAULT NULL,
`customer_id` varchar(31) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
`search_text` varchar(255) DEFAULT NULL,
`tenant_id` varchar(31) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `tenant` (
`id