405 lines
10 KiB
Go
405 lines
10 KiB
Go
package main
|
||
|
||
import (
|
||
"database/sql"
|
||
"fmt"
|
||
"strings"
|
||
"time"
|
||
|
||
_ "github.com/go-sql-driver/mysql"
|
||
)
|
||
|
||
var db *sql.DB
|
||
|
||
const SCALING_FACTOR = 1000 // 浮点数到整数的转换因子
|
||
|
||
// 初始化数据库连接
|
||
func InitDB() error {
|
||
username := "remote"
|
||
password := "root"
|
||
host := "8.134.185.53"
|
||
port := "3306"
|
||
dbName := "probe_db"
|
||
|
||
// 连接字符串
|
||
dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?parseTime=true&loc=Asia%%2FShanghai", username, password, host, port, dbName)
|
||
|
||
var err error
|
||
db, err = sql.Open("mysql", dsn)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
|
||
err = db.Ping()
|
||
if err != nil {
|
||
return err
|
||
}
|
||
|
||
db.SetMaxOpenConns(10)
|
||
db.SetMaxIdleConns(5)
|
||
db.SetConnMaxLifetime(time.Minute * 5)
|
||
|
||
return nil
|
||
}
|
||
|
||
// 关闭数据库连接
|
||
func CloseDB() {
|
||
if db != nil {
|
||
db.Close()
|
||
}
|
||
}
|
||
|
||
// EnsureDeviceExists 确保设备存在，不存在则创建为默认配置
|
||
func EnsureDeviceExists(deviceID string) error {
|
||
if strings.TrimSpace(deviceID) == "" {
|
||
return nil
|
||
}
|
||
_, err := db.Exec(
|
||
"INSERT INTO devices (device_id, forward_enable, host, port) VALUES (?, 0, NULL, NULL) ON DUPLICATE KEY UPDATE device_id = device_id",
|
||
deviceID,
|
||
)
|
||
return err
|
||
}
|
||
|
||
// 保存传感器数据 - 将浮点值转换为整数存储，添加温度与设备ID（字符串）支持
|
||
func SaveSensorData(sensorID int, x, y, z, temperature float64, deviceID string) error {
|
||
xInt := int(x * SCALING_FACTOR)
|
||
yInt := int(y * SCALING_FACTOR)
|
||
zInt := int(z * SCALING_FACTOR)
|
||
tempInt := int(temperature * SCALING_FACTOR)
|
||
|
||
if strings.TrimSpace(deviceID) != "" {
|
||
query := `INSERT INTO sensor_data (sensor_id, x_value, y_value, z_value, temperature, device_id) VALUES (?, ?, ?, ?, ?, ?)`
|
||
_, err := db.Exec(query, sensorID, xInt, yInt, zInt, tempInt, deviceID)
|
||
return err
|
||
}
|
||
|
||
query := `INSERT INTO sensor_data (sensor_id, x_value, y_value, z_value, temperature, device_id) VALUES (?, ?, ?, ?, ?, NULL)`
|
||
_, err := db.Exec(query, sensorID, xInt, yInt, zInt, tempInt)
|
||
return err
|
||
}
|
||
|
||
// 获取传感器数据 - 添加时间范围，包含温度字段
|
||
func GetSensorData(sensorID int, limit int, startDate time.Time, endDate time.Time) ([]SensorData, error) {
|
||
query := "SELECT id, sensor_id, x_value, y_value, z_value, COALESCE(temperature, 0) as temperature, `timestamp` as timestamp FROM sensor_data WHERE sensor_id = ?"
|
||
|
||
var args []interface{}
|
||
args = append(args, sensorID)
|
||
|
||
if !startDate.IsZero() {
|
||
query += " AND `timestamp` >= ?"
|
||
args = append(args, startDate)
|
||
}
|
||
|
||
if !endDate.IsZero() {
|
||
query += " AND `timestamp` <= ?"
|
||
args = append(args, endDate)
|
||
}
|
||
|
||
query += " ORDER BY `timestamp` DESC"
|
||
|
||
// 只有当limit > 0时才添加LIMIT子句
|
||
if limit > 0 {
|
||
query += " LIMIT ?"
|
||
args = append(args, limit)
|
||
}
|
||
|
||
rows, err := db.Query(query, args...)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
var result []SensorData
|
||
|
||
for rows.Next() {
|
||
var data SensorData
|
||
var xInt, yInt, zInt, tempInt int
|
||
|
||
err := rows.Scan(&data.ID, &data.SensorID, &xInt, &yInt, &zInt, &tempInt, &data.Timestamp)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
data.X = float64(xInt) / SCALING_FACTOR
|
||
data.Y = float64(yInt) / SCALING_FACTOR
|
||
data.Z = float64(zInt) / SCALING_FACTOR
|
||
data.Temperature = float64(tempInt) / SCALING_FACTOR
|
||
|
||
result = append(result, data)
|
||
}
|
||
|
||
return result, nil
|
||
}
|
||
|
||
// 获取所有传感器数据，包含温度字段
|
||
func GetAllSensorData(limit int, startDate time.Time, endDate time.Time) ([]SensorData, error) {
|
||
query := "SELECT id, sensor_id, x_value, y_value, z_value, COALESCE(temperature, 0) as temperature, `timestamp` as timestamp FROM sensor_data WHERE 1=1"
|
||
|
||
var args []interface{}
|
||
|
||
if !startDate.IsZero() {
|
||
query += " AND `timestamp` >= ?"
|
||
args = append(args, startDate)
|
||
}
|
||
|
||
if !endDate.IsZero() {
|
||
query += " AND `timestamp` <= ?"
|
||
args = append(args, endDate)
|
||
}
|
||
|
||
query += " ORDER BY `timestamp` DESC"
|
||
|
||
// 只有当limit > 0时才添加LIMIT子句
|
||
if limit > 0 {
|
||
query += " LIMIT ?"
|
||
args = append(args, limit)
|
||
}
|
||
|
||
rows, err := db.Query(query, args...)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
var result []SensorData
|
||
|
||
for rows.Next() {
|
||
var data SensorData
|
||
var xInt, yInt, zInt, tempInt int
|
||
|
||
err := rows.Scan(&data.ID, &data.SensorID, &xInt, &yInt, &zInt, &tempInt, &data.Timestamp)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
data.X = float64(xInt) / SCALING_FACTOR
|
||
data.Y = float64(yInt) / SCALING_FACTOR
|
||
data.Z = float64(zInt) / SCALING_FACTOR
|
||
data.Temperature = float64(tempInt) / SCALING_FACTOR
|
||
|
||
result = append(result, data)
|
||
}
|
||
|
||
return result, nil
|
||
}
|
||
|
||
// 获取所有传感器ID
|
||
func GetAllSensorIDs() ([]int, error) {
|
||
query := `SELECT DISTINCT sensor_id FROM sensor_data ORDER BY sensor_id`
|
||
|
||
rows, err := db.Query(query)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
var ids []int
|
||
for rows.Next() {
|
||
var id int
|
||
if err := rows.Scan(&id); err != nil {
|
||
return nil, err
|
||
}
|
||
ids = append(ids, id)
|
||
}
|
||
|
||
return ids, nil
|
||
}
|
||
|
||
// SensorData 结构用于存储传感器数据，添加温度字段
|
||
type SensorData struct {
|
||
ID int `json:"id"`
|
||
SensorID int `json:"sensor_id"`
|
||
X float64 `json:"x"`
|
||
Y float64 `json:"y"`
|
||
Z float64 `json:"z"`
|
||
Temperature float64 `json:"temperature"`
|
||
Timestamp time.Time `json:"timestamp"`
|
||
}
|
||
|
||
// Device 表映射
|
||
type Device struct {
|
||
ID int
|
||
DeviceID string
|
||
ForwardEnable bool
|
||
Host sql.NullString
|
||
Port sql.NullInt64
|
||
RegCodeHex sql.NullString
|
||
}
|
||
|
||
// DeviceWithStats 包含设备统计信息
|
||
type DeviceWithStats struct {
|
||
ID int
|
||
DeviceID string
|
||
ForwardEnable bool
|
||
Host sql.NullString
|
||
Port sql.NullInt64
|
||
RegCodeHex sql.NullString
|
||
SensorCount int
|
||
LastSeen sql.NullTime
|
||
}
|
||
|
||
// GetDevice 获取设备配置（按设备字符串ID）
|
||
func GetDevice(deviceID string) (*Device, error) {
|
||
row := db.QueryRow(`SELECT id, device_id, COALESCE(forward_enable, 0) as forward_enable, host, port, reg_code_hex FROM devices WHERE device_id = ?`, deviceID)
|
||
var d Device
|
||
var fe int
|
||
if err := row.Scan(&d.ID, &d.DeviceID, &fe, &d.Host, &d.Port, &d.RegCodeHex); err != nil {
|
||
if err == sql.ErrNoRows {
|
||
return nil, nil
|
||
}
|
||
return nil, err
|
||
}
|
||
d.ForwardEnable = fe != 0
|
||
return &d, nil
|
||
}
|
||
|
||
// GetDevicesWithStats 获取设备列表及统计
|
||
func GetDevicesWithStats() ([]DeviceWithStats, error) {
|
||
query := `SELECT d.id,
|
||
d.device_id,
|
||
COALESCE(d.forward_enable, 0) AS forward_enable,
|
||
d.host,
|
||
d.port,
|
||
d.reg_code_hex,
|
||
COALESCE(COUNT(DISTINCT sd.sensor_id), 0) AS sensor_count,
|
||
MAX(sd.timestamp) AS last_seen
|
||
FROM devices d
|
||
LEFT JOIN sensor_data sd ON sd.device_id = d.device_id
|
||
GROUP BY d.id, d.device_id, d.forward_enable, d.host, d.port, d.reg_code_hex
|
||
ORDER BY d.id`
|
||
|
||
rows, err := db.Query(query)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
var list []DeviceWithStats
|
||
for rows.Next() {
|
||
var item DeviceWithStats
|
||
var fe int
|
||
if err := rows.Scan(&item.ID, &item.DeviceID, &fe, &item.Host, &item.Port, &item.RegCodeHex, &item.SensorCount, &item.LastSeen); err != nil {
|
||
return nil, err
|
||
}
|
||
item.ForwardEnable = fe != 0
|
||
list = append(list, item)
|
||
}
|
||
return list, nil
|
||
}
|
||
|
||
// GetSensorIDsByDevice 获取某设备下的传感器ID
|
||
func GetSensorIDsByDevice(deviceID string) ([]int, error) {
|
||
rows, err := db.Query(`SELECT DISTINCT sensor_id FROM sensor_data WHERE device_id = ? ORDER BY sensor_id`, deviceID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
var ids []int
|
||
for rows.Next() {
|
||
var id int
|
||
if err := rows.Scan(&id); err != nil {
|
||
return nil, err
|
||
}
|
||
ids = append(ids, id)
|
||
}
|
||
return ids, nil
|
||
}
|
||
|
||
// GetSensorDataByDevice 获取某设备下的数据（可选时间范围）
|
||
func GetSensorDataByDevice(deviceID string, limit int, startDate, endDate time.Time) ([]SensorData, error) {
|
||
query := "SELECT id, sensor_id, x_value, y_value, z_value, " +
|
||
"COALESCE(temperature, 0) as temperature, " +
|
||
"`timestamp` as timestamp " +
|
||
"FROM sensor_data " +
|
||
"WHERE device_id = ?"
|
||
|
||
var args []interface{}
|
||
args = append(args, deviceID)
|
||
|
||
if !startDate.IsZero() {
|
||
query += " AND `timestamp` >= ?"
|
||
args = append(args, startDate)
|
||
}
|
||
if !endDate.IsZero() {
|
||
query += " AND `timestamp` <= ?"
|
||
args = append(args, endDate)
|
||
}
|
||
|
||
query += " ORDER BY `timestamp` DESC"
|
||
if limit > 0 {
|
||
query += " LIMIT ?"
|
||
args = append(args, limit)
|
||
}
|
||
|
||
rows, err := db.Query(query, args...)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
var result []SensorData
|
||
for rows.Next() {
|
||
var data SensorData
|
||
var xInt, yInt, zInt, tempInt int
|
||
if err := rows.Scan(&data.ID, &data.SensorID, &xInt, &yInt, &zInt, &tempInt, &data.Timestamp); err != nil {
|
||
return nil, err
|
||
}
|
||
data.X = float64(xInt) / SCALING_FACTOR
|
||
data.Y = float64(yInt) / SCALING_FACTOR
|
||
data.Z = float64(zInt) / SCALING_FACTOR
|
||
data.Temperature = float64(tempInt) / SCALING_FACTOR
|
||
result = append(result, data)
|
||
}
|
||
return result, nil
|
||
}
|
||
|
||
// GetSensorDataByDeviceAndSensor 获取某设备某传感器的数据
|
||
func GetSensorDataByDeviceAndSensor(deviceID string, sensorID int, limit int, startDate, endDate time.Time) ([]SensorData, error) {
|
||
query := "SELECT id, sensor_id, x_value, y_value, z_value, " +
|
||
"COALESCE(temperature, 0) as temperature, " +
|
||
"`timestamp` as timestamp " +
|
||
"FROM sensor_data " +
|
||
"WHERE device_id = ? AND sensor_id = ?"
|
||
|
||
var args []interface{}
|
||
args = append(args, deviceID, sensorID)
|
||
|
||
if !startDate.IsZero() {
|
||
query += " AND `timestamp` >= ?"
|
||
args = append(args, startDate)
|
||
}
|
||
if !endDate.IsZero() {
|
||
query += " AND `timestamp` <= ?"
|
||
args = append(args, endDate)
|
||
}
|
||
|
||
query += " ORDER BY `timestamp` DESC"
|
||
if limit > 0 {
|
||
query += " LIMIT ?"
|
||
args = append(args, limit)
|
||
}
|
||
|
||
rows, err := db.Query(query, args...)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
var result []SensorData
|
||
for rows.Next() {
|
||
var data SensorData
|
||
var xInt, yInt, zInt, tempInt int
|
||
if err := rows.Scan(&data.ID, &data.SensorID, &xInt, &yInt, &zInt, &tempInt, &data.Timestamp); err != nil {
|
||
return nil, err
|
||
}
|
||
data.X = float64(xInt) / SCALING_FACTOR
|
||
data.Y = float64(yInt) / SCALING_FACTOR
|
||
data.Z = float64(zInt) / SCALING_FACTOR
|
||
data.Temperature = float64(tempInt) / SCALING_FACTOR
|
||
result = append(result, data)
|
||
}
|
||
return result, nil
|
||
}
|