thingsboard 改为 mysql 数据库运行 · 物联网平台-威尼斯人最新

感谢：guanmaoyun

由于项目需要我公司需要开发一个物联网项目，之前也没有相应的案例，所以从网上找到开源的 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` 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,
  `region` varchar(255) default null,
  `search_text` varchar(255) default null,
  `state` varchar(255) default null,
  `title` varchar(255) default null,
  `zip` varchar(255) default null,
  primary key (`id`)
) engine=innodb default charset=utf8;
create table `tenant_ts_kv` (
  `tenant_id` varchar(31) not null,
  `entity_id` varchar(31) not null,
  `key` varchar(255) not null,
  `ts` bigint(20) not null,
  `bool_v` tinyint(1) default null,
  `str_v` varchar(255) default null,
  `long_v` bigint(20) default null,
  `dbl_v` double default null,
  primary key (`tenant_id`,`entity_id`,`key`,`ts`)
) engine=innodb default charset=utf8;
create table `ts_kv` (
  `entity_type` varchar(255) not null,
  `entity_id` varchar(31) not null,
  `key` varchar(255) not null,
  `ts` bigint(20) not null,
  `bool_v` tinyint(1) default null,
  `str_v` varchar(255) default null,
  `long_v` bigint(20) default null,
  `dbl_v` double default null,
  primary key (`entity_type`,`entity_id`,`key`,`ts`)
) engine=innodb default charset=utf8;
create table `ts_kv_latest` (
  `entity_type` varchar(255) not null,
  `entity_id` varchar(31) not null,
  `key` varchar(255) not null,
  `ts` bigint(20) not null,
  `bool_v` tinyint(1) default null,
  `str_v` varchar(255) default null,
  `long_v` bigint(20) default null,
  `dbl_v` double default null,
  primary key (`entity_type`,`entity_id`,`key`)
) engine=innodb default charset=utf8;
create table `user_credentials` (
  `id` varchar(31) not null,
  `activate_token` varchar(255) default null,
  `enabled` tinyint(1) default null,
  `password` varchar(255) default null,
  `reset_token` varchar(255) default null,
  `user_id` varchar(31) default null,
  primary key (`id`),
  unique key `activate_token` (`activate_token`),
  unique key `reset_token` (`reset_token`),
  unique key `user_id` (`user_id`)
) engine=innodb default charset=utf8;
create table `widget_type` (
  `id` varchar(31) not null,
  `alias` varchar(255) default null,
  `bundle_alias` varchar(255) default null,
  `descriptor` varchar(10000) default null,
  `name` varchar(255) default null,
  `tenant_id` varchar(31) default null,
  primary key (`id`)
) engine=innodb default charset=utf8;
create table `widgets_bundle` (
  `id` varchar(31) not null,
  `alias` 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;

插入基础数据：

insert into `admin_settings` (`id`, `json_value`, `key`) values ('1e746126a2266e4a91992ebcb67fe33', '{\r\n    \"baseurl\": \"http://localhost:8080\"\r\n}', 'general');
insert into `user_credentials` (`id`, `activate_token`, `enabled`, `password`, `reset_token`, `user_id`) values ('1e7461261441950a91992ebcb67fe33', null, '1', '$2a$10$5jtb8/hxwc9way62ncgsxeefl3kwmipa9nfpvdda0/xfiseebb4bu', null, '1e746125a797660a91992ebcb67fe33');
insert into `tb_user` (`id`, `additional_info`, `authority`, `customer_id`, `email`, `first_name`, `last_name`, `search_text`, `tenant_id`) values ('1e746125a797660a91992ebcb67fe33', '{\"lastlogints\":1581066533969,\"failedloginattempts\":0}', 'sys_admin', '1b21dd2138140008080808080808080', 'sysadmin@thingsboard.org', null, null, 'sysadmin@thingsboard.org', '1b21dd2138140008080808080808080');

表建好后启动 idea，第一个问题：无法导入 mysql 数据库驱动，在项目根目录 pom 文件中加入了 mysql 驱动，可是无论如何也不能自动下载依赖，找不到原因，没办法只能手动导入

其他配置请到社区官方 q 群获取