ServerDemon and MySql
directory
1 Overview
Located in the ManageTools solution, ServerDemon is a C#-based, cross-platform console application that can be deployed as an on-server server for Windows and Linux, and there are also cases where docker is used.
The function of ServerDemon is very simple, save all messages received by MQTT to the MySql database. The database schema is simple, involving only a few simple tables, but it's strong enough to support querying data by criteria such as gateway and message category.
The code of ServerDemon is very simple, mainly the connection between MQTT and MySql, and the connection parameters are in the code, which can be modified as needed.
There is a MySql icon button on the ManageTool interface, which can be clicked to query the database information.
There is also an InfluxDB icon button on the ManageTool interface, and if you enable the InfluxDB related code in the ServerDemon code, you can also insert data into InfluxDB.
2 MySql
In order to quickly verify the system, you can install the MySql database locally, and version 9.01 is recommended (of course, it does not use any rare database features at all, and either version should be fine).
The default connection mode in the source code is: 127.0.0.1, the default port, database name "gateway", and the username and password are "user1".
3 Database Scripts
CREATE DATABASE IF NOT EXISTS `gateway` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `gateway`;
-- MySQL dump 10.13 Distrib 8.0.38, for Win64 (x86_64)
--
-- Host: 127.0.0.1 Database: gateway
-- ------------------------------------------------------
-- Server version 9.0.1
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `gw_config`
--
DROP TABLE IF EXISTS `gw_config`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `gw_config` (
`gw_sn` varchar(30) NOT NULL COMMENT '网关序列号',
'gw_model' varchar(30) DEFAULT NULL KWORD 'SAEED',
`gw_iccid` varchar(30) DEFAULT NULL COMMENT 'ICCID（如果有）',
`sw_version` varchar(45) DEFAULT NULL COMMENT '软件版本',
`confirmed_data_sequence` int DEFAULT NULL COMMENT '已确认的数据序列',
`last_data_sequence` int DEFAULT NULL COMMENT '最新数据序列',
'use_gm' int DEFAULT NULL COMMENT 'synopsis',
'key_seq' int DEFAULT NULL COMMENT '??????(??????)',
PRIMARY KEY (`gw_sn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='网关配置（平台侧）';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `gw_config`
--
LOCK TABLES `gw_config` WRITE;
/*!40000 ALTER TABLE `gw_config` DISABLE KEYS */;
INSERT INTO 'gw_config' VALUES ('GW-5G-VM-DELL','GW-5G',ZERO,ZERO,ZERO,ZERO,ZERO,ZERO);
/*!40000 ALTER TABLE `gw_config` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `gw_key`
--
DROP TABLE IF EXISTS `gw_key`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `gw_key` (
`gw_sn` varchar(30) NOT NULL COMMENT '网关序列号',
`gw_key_seq` int NOT NULL COMMENT 'key标识',
'gw_key' varchar(128) DEFAULT NULL HOW 'key',
PRIMARY KEY (`gw_sn`,`gw_key_seq`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='网关key信息';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `gw_key`
--
LOCK TABLES `gw_key` WRITE;
/*!40000 ALTER TABLE `gw_key` DISABLE KEYS */;
INSERT INTO `gw_key` VALUES ('GW-5G-VM-DELL',0,'123'),('GW-5G-VM-DELL',1,'12345'),('GW-5G-VM-DELL',2,'1234567');
/*!40000 ALTER TABLE `gw_key` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `gw_message_list`
--
DROP TABLE IF EXISTS `gw_message_list`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `gw_message_list` (
`id` int NOT NULL AUTO_INCREMENT,
`gw_sn` varchar(30) DEFAULT NULL,
`func` varchar(30) DEFAULT NULL,
`ts_str` varchar(30) DEFAULT NULL,
'insert_time' datetime DEFAULT CURRENT_TIMESTAMP,
`topic` varchar(255) DEFAULT NULL,
'message' mediumtext COMMENT 'Message body, extra-long part is discarded',
'uid' varchar(45) DEFAULT NULL COMMENT '消息标识',
`respond_for_uid` varchar(45) DEFAULT NULL COMMENT '如果是应答，针对的uid',
`data_seq` int DEFAULT NULL,
`summary` varchar(255) DEFAULT NULL COMMENT 'Summary, parts that exceed length are discarded',
PRIMARY KEY (`id`),
KEY `i_sn` (`gw_sn`,`insert_time`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='网关消息列表';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `gw_message_list`
--
LOCK TABLES `gw_message_list` WRITE;
/*!40000 ALTER TABLE `gw_message_list` DISABLE KEYS */;
/*!40000 ALTER TABLE `gw_message_list` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `gw_report_info`
--
DROP TABLE IF EXISTS `gw_report_info`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `gw_report_info` (
`gw_sn` varchar(30) NOT NULL COMMENT '网关序列号',
'gw_model' varchar(30) DEFAULT NULL KWORD 'SAEED',
`gw_iccid` varchar(30) DEFAULT NULL COMMENT 'ICCID（如果有）',
`sw_version` varchar(45) DEFAULT NULL COMMENT '软件版本',
`confirmed_data_sequence` int DEFAULT NULL COMMENT '已确认的数据序列',
`last_data_sequence` int DEFAULT NULL COMMENT '最新数据序列',
`start_time` varchar(30) DEFAULT NULL COMMENT '启动时间（程序）',
`last_time` varchar(30) DEFAULT NULL COMMENT 'The time of the last reported information (including the reported data)'
`last_data_time` varchar(30) DEFAULT NULL COMMENT 'WHEN DATA WAS LAST REPORTED',
'use_gm' int DEFAULT NULL COMMENT 'synopsis',
'key_seq' int DEFAULT NULL COMMENT '??????(??????)',
'current_key_seq' int DEFAULT NULL KWORD '???(??????)',
PRIMARY KEY (`gw_sn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='网关列表';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `gw_report_info`
--
LOCK TABLES `gw_report_info` WRITE;
/*!40000 ALTER TABLE `gw_report_info` DISABLE KEYS */;
/*!40000 ALTER TABLE `gw_report_info` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tenant_gw`
--
DROP TABLE IF EXISTS `tenant_gw`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tenant_gw` (
'tenant_name' varchar(64) NOT NULL HOW '租户名称',
`gw_sn` varchar(30) NOT NULL COMMENT '网关序列号',
`gw_north_config_name` varchar(64) DEFAULT NULL COMMENT '网关北向配置',
`gw_source_config_name` varchar(64) DEFAULT NULL COMMENT '网关南向配置',
`gw_hardware_config_name` varchar(64) DEFAULT NULL COMMENT '网关硬件配置',
PRIMARY KEY (`tenant_name`,`gw_sn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='租户的网关';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tenant_gw`
--
LOCK TABLES `tenant_gw` WRITE;
/*!40000 ALTER TABLE `tenant_gw` DISABLE KEYS */;
/*!40000 ALTER TABLE `tenant_gw` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tenant_hardware_config`
--
DROP TABLE IF EXISTS `tenant_hardware_config`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tenant_hardware_config` (
'tenant_name' varchar(64) NOT NULL HOW '租户名称',
`hardware_config_name` varchar(64) NOT NULL,
`hardware_cinfig_data` varchar(16000) DEFAULT NULL,
PRIMARY KEY (`tenant_name`,`hardware_config_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='硬件配置';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tenant_hardware_config`
--
LOCK TABLES `tenant_hardware_config` WRITE;
/*!40000 ALTER TABLE `tenant_hardware_config` DISABLE KEYS */;
/*!40000 ALTER TABLE `tenant_hardware_config` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tenant_info`
--
DROP TABLE IF EXISTS `tenant_info`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tenant_info` (
'tenant_name' varchar(64) NOT NULL HOW '租户名称',
'tenant_contact' varchar(45) DEFAULT NULL HOW '联系人',
`tenant_phone_number` varchar(45) DEFAULT NULL COMMENT '电话',
PRIMARY KEY (`tenant_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='租户信息';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tenant_info`
--
LOCK TABLES `tenant_info` WRITE;
/*!40000 ALTER TABLE `tenant_info` DISABLE KEYS */;
INSERT INTO `tenant_info` VALUES ('default_tenant','联系人','电话');
/*!40000 ALTER TABLE `tenant_info` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tenant_north_config`
--
DROP TABLE IF EXISTS `tenant_north_config`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tenant_north_config` (
'tenant_name' varchar(64) NOT NULL HOW '租户名称',
`north_config_name` varchar(32) NOT NULL COMMENT '配置名称',
'north_type' varchar(45) DEFAULT NULL HOW 'MQTT/MQTTS',
`host` varchar(45) DEFAULT NULL COMMENT '地址',
'port' int DEFAULT NULL COMMENT '端口',
'user' varchar(45) DEFAULT NULL COMMENT '用户名',
'passowrd' varchar(45) DEFAULT NULL HOW '密码',
PRIMARY KEY (`tenant_name`,`north_config_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='租户的北向配置';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tenant_north_config`
--
LOCK TABLES `tenant_north_config` WRITE;
/*!40000 ALTER TABLE `tenant_north_config` DISABLE KEYS */;
/*!40000 ALTER TABLE `tenant_north_config` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tenant_south_config`
--
DROP TABLE IF EXISTS `tenant_south_config`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tenant_south_config` (
'tenant_name' varchar(64) NOT NULL HOW '租户名称',
`south_config_name` varchar(64) NOT NULL,
`south_config_data` mediumtext COMMENT '配置数据',
PRIMARY KEY (`tenant_name`,`south_config_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='南向配置(数采)';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tenant_south_config`
--
LOCK TABLES `tenant_south_config` WRITE;
/*!40000 ALTER TABLE `tenant_south_config` DISABLE KEYS */;
/*!40000 ALTER TABLE `tenant_south_config` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2025-04-29 12:03:05
4 Database Design
4.1 Table Summary
|
name |
illustrate |
|
gw_config |
Gateway Configuration (Platform Side) |
|
gw_report_info |
Gateway Reporting Information (Gateway-Side Configuration) |
|
gw_message_list |
List of gateway messages |
|
gw_key |
Gateway key information |
|
tenant_info |
tenant |
|
tenant_gw |
Tenant-owned gateways |
|
tenant_north_config |
Platform Connectivity Configuration (MQTT) |
|
tenant_sourth_config |
Device connection configuration (MODBUS, etc.) |
|
tenant_hardware_config |
Hardware Configuration (IP) |
4.2 Concept Map
4.3 gw_config
The configuration information on the gateway platform may be inconsistent with the actual configuration of the gateway.
|
Listing |
empty |
type |
illustrate |
|
confirmed_data_sequence |
YES |
int |
Confirmed data series |
|
gw_iccid |
YES |
varchar(30) |
ICCID (if available). |
|
gw_model |
YES |
varchar(30) |
Model |
|
gw_sn |
NO |
varchar(30) |
Gateway serial number |
|
key_seq |
YES |
int |
Key ID (set value) |
|
last_data_sequence |
YES |
int |
The latest data series |
|
sw_version |
YES |
varchar(45) |
Software Version |
|
use_gm |
YES |
int |
Whether or not to use national secrets |
PK:gw_sn
4.4 gw_report_info
The information reported by the gateway is consistent with the actual information of the gateway but may not be consistent with the platform configuration.
|
Listing |
empty |
type |
illustrate |
|
confirmed_data_sequence |
YES |
int |
Confirmed data series |
|
current_key_seq |
YES |
int |
Current Key ID (Usage Value) |
|
gw_iccid |
YES |
varchar(30) |
ICCID (if available). |
|
gw_model |
YES |
varchar(30) |
Model |
|
gw_sn |
NO |
varchar(30) |
Gateway serial number |
|
key_seq |
YES |
int |
Key ID (set value) |
|
last_data_sequence |
YES |
int |
The latest data series |
|
last_data_time |
YES |
varchar(30) |
The last time the data was reported |
|
last_time |
YES |
varchar(30) |
The last time the information was reported (including the reported data) |
|
start_time |
YES |
varchar(30) |
Startup Time (Program) |
|
sw_version |
YES |
varchar(45) |
Software Version |
|
use_gm |
YES |
int |
Whether or not to use national secrets |
PK:gw_sn
4.5 gw_message_list
Gateway messages (extra-long messages are truncated).
|
Listing |
empty |
type |
illustrate |
|
data_seq |
YES |
int |
|
|
func |
YES |
varchar(30) |
|
|
gw_sn |
YES |
varchar(30) |
|
|
id |
NO |
int |
Auto-increment |
|
insert_time |
YES |
datetime |
|
|
message |
YES |
Medium text |
The message body, the extra-long part is discarded |
|
respond_for_uid |
YES |
varchar(45) |
If it is an answer, the UID for it |
|
summary |
YES |
varchar(255) |
Abstract, the portion that exceeds the length is discarded |
|
topic |
YES |
varchar(255) |
|
|
ts_str |
YES |
varchar(30) |
|
|
uid |
YES |
varchar(45) |
Message ID |
PK：id
Index:gw_sn,insert_time(不唯一)
4.6 gw_key
Gateway key sequence.
|
Listing |
empty |
type |
illustrate |
|
gw_key |
YES |
varchar(128) |
key |
|
gw_key_seq |
NO |
int |
key identifier |
|
gw_sn |
NO |
varchar(30) |
Gateway serial number |
PK:gw_sn,gw_key_seq
4.7 tenant_info
|
Listing |
empty |
type |
illustrate |
|
tenant_contact |
YES |
varchar(45) |
Contact |
|
tenant_name |
NO |
varchar(64) |
Tenant name |
|
tenant_phone_number |
YES |
varchar(45) |
Phone |
4.8 tenant_gw
|
Listing |
empty |
type |
illustrate |
|
tenant_name |
NO |
varchar(64) |
Tenant name |
|
gw_hardware_config_name |
YES |
varchar(64) |
Gateway hardware configuration |
|
gw_north_config_name |
YES |
varchar(64) |
Northbound configuration of the gateway |
|
gw_sn |
NO |
varchar(30) |
Gateway serial number |
|
gw_source_config_name |
YES |
varchar(64) |
Gateway southbound configuration |
|
tenant_name |
NO |
varchar(64) |
Tenant name |
4.9 tenant_north_config
|
Listing |
empty |
type |
illustrate |
|
tenant_name |
NO |
varchar(64) |
Tenant name |
|
host |
YES |
varchar(45) |
address |
|
north_config_name |
NO |
varchar(32) |
Configuration name |
|
north_type |
YES |
varchar(45) |
MQTT/MQTTS |
|
passowrd |
YES |
varchar(45) |
password |
|
port |
YES |
int |
port |
|
user |
YES |
varchar(45) |
Username |
4.10 tenant_sourth_config
|
Listing |
empty |
type |
illustrate |
|
south_config_data |
YES |
Medium text |
Configuration data |
|
south_config_name |
NO |
varchar(64) |
|
|
tenant_name |
NO |
varchar(64) |
Tenant name |
4.11 tenant_hardware_config
|
Listing |
empty |
type |
illustrate |
|
hardware_cinfig_data |
YES |
varchar(16000) |
|
|
hardware_config_name |
NO |
varchar(64) |
|
|
tenant_name |
NO |
varchar(64) |
Tenant name |
5 Examples of use
5.1 编译ServerDemon
Note that ServerDemon and ManageTools have two identical nuget dependencies, and the MySQL related parts are identical.
It can be run directly in Visual Studio, or it can be packaged and run on a Windows or Linux server.
5.2 运行ServerDemon
If there is nothing wrong with the connection parameters, everything should be fine.
Run logs:
2025-04-29 13:28:25.620 tid 主线程 [Info ]C:\working\ctGateway\ManageTools\ServerDemon\Program.cs :55行 :ServerDemon 2024.09.13 16:39
2025-04-29 13:28:25.633 tid 主线程 [Info ]C:\working\ctGateway\ManageTools\ServerDemon\Program.cs :58行 :-debug
2025-04-29 13:28:25.633 tid 主线程 [Info ]C:\working\ctGateway\ManageTools\ServerDemon\Program.cs :71行 :调试模式：True
2025-04-29 13:28:25.634 tid 主线程 [Info ]C:\working\ctGateway\ManageTools\ServerDemon\Program.cs :72行 :BaseInfluxDBTools.url =
2025-04-29 13:28:26.546 tid 主线程 [Info ]C:\working\ctGateway\ManageTools\ServerDemon\Program.cs :19行 :GW-5G-VM-DELL
2025-04-29 13:28:26.547 tid 主线程 [Info ]C:\working\ctGateway\ManageTools\ServerDemon\Program.cs :19行 :GW-5G-VM-DELL
2025-04-29 13:28:26.547 tid 主线程 [Info ]C:\working\ctGateway\ManageTools\ServerDemon\Program.cs :19行 :GW-5G-VM-DELL
2025-04-29 13:28:26.624 tid 主线程 [Info ]C:\working\ctGateway\ManageTools\ServerDemon\Program.cs :27行 :mysql连接成功
2025-04-29 13:28:26.626 tid main thread [info.] ]: 0 rows: total number of records 3 error 0
2025-04-29 13:28:26.650 tid 主线程 [Info ]C:\working\ctGateway\ManageTools\ServerDemon\Program.cs :48行 :Mqtt客户端正在连接......
2025-04-29 13:28:26.657 tid 主线程 [Info ]C:\working\ctGateway\ManageTools\ServerDemon\Program.cs :91行 :成功连接到 localtest MQTTS 127.0.0.1
2025-04-29 13:28:26.706 tid 11 [Info ]C:\working\ctGateway\ManageTools\ServerDemon\Program.cs :48行 :Mqtt客户端连接成功(1).
2025-04-29 13:28:26.717 tid 11 [Info] :0:d evice_gateway/reg
2025-04-29 13:28:26.718 tid 11 [Info] :0:d evice_gateway/#
2025-04-29 13:28:40.794 tid 06 [Debug]: Line 0: Message received from [GW-5G-VM-DELL].
2025-04-29 13:28:40.796 tid 06 [Info ]C:\working\ctGateway\ManageTools\ServerDemon\Program.cs :37行 :GW-5G-VM-DELL
5.3 View from ManageTools
There is a MySql icon on the main interface:
Click on the MySql icon:
The function is actually quite messy, and it hasn't been seriously designed, which means it.
View all messages for the gateway:
If the gateway is not in the gw_config, you can use the menu "Gateway Management" - "Update Registration Information to Configuration" to add the selected gateway to the gw_config.
5.4 View from MySql
The main thing is the message list gw_message_list:
