using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Linq;
|
using System.Security.Cryptography;
|
using System.Text;
|
using HMI.Core.Util;
|
using Plugin.DeviceConfig.Entity;
|
|
namespace Plugin.DeviceConfig.Model
|
{
|
/// <summary>
|
/// 设备配置数据访问层，基于 SQLite 实现
|
/// </summary>
|
public class DeviceConfigModel
|
{
|
private string _dbPath = string.Empty;
|
|
/// <summary>
|
/// 初始化数据库，创建所有表
|
/// </summary>
|
/// <param name="dbPath">数据库文件路径</param>
|
public void InitDatabase(string dbPath)
|
{
|
_dbPath = dbPath;
|
try
|
{
|
// 确保目录存在
|
string? dir = System.IO.Path.GetDirectoryName(dbPath);
|
if (!string.IsNullOrEmpty(dir) && !System.IO.Directory.Exists(dir))
|
{
|
System.IO.Directory.CreateDirectory(dir);
|
}
|
|
// 仅在数据库文件不存在时创建（避免重启后抛异常或丢失已有数据）
|
if (!System.IO.File.Exists(dbPath))
|
{
|
SqliteHelper.CreateDatabase(dbPath);
|
}
|
|
using var helper = new SqliteHelper(dbPath);
|
|
var sqls = new List<string>
|
{
|
// 设备定义表
|
@"CREATE TABLE IF NOT EXISTS DeviceDefinition (
|
DeviceId TEXT PRIMARY KEY,
|
Kind TEXT NOT NULL,
|
DriverType TEXT NOT NULL,
|
Transport TEXT NOT NULL,
|
Endpoint TEXT NOT NULL,
|
EffectiveVersion TEXT NOT NULL DEFAULT '',
|
CreatedAt TEXT NOT NULL,
|
UpdatedAt TEXT NOT NULL
|
);",
|
|
// 设备通道表
|
@"CREATE TABLE IF NOT EXISTS DeviceChannel (
|
DeviceId TEXT NOT NULL,
|
ChannelId INTEGER NOT NULL,
|
Capability TEXT NOT NULL DEFAULT '',
|
MaxVoltage REAL NOT NULL DEFAULT 0,
|
MaxCurrent REAL NOT NULL DEFAULT 0,
|
Limits TEXT NOT NULL DEFAULT '{}',
|
SessionKey TEXT NOT NULL DEFAULT '',
|
PRIMARY KEY (DeviceId, ChannelId)
|
);",
|
|
// Slot映射表
|
@"CREATE TABLE IF NOT EXISTS SlotDeviceMapping (
|
SlotId TEXT NOT NULL,
|
Capability TEXT NOT NULL,
|
Version TEXT NOT NULL,
|
DeviceId TEXT NOT NULL,
|
ChannelId INTEGER NOT NULL DEFAULT 0,
|
MappingVersion TEXT NOT NULL DEFAULT '',
|
PRIMARY KEY (SlotId, Capability, Version)
|
);",
|
|
// 配置版本表
|
@"CREATE TABLE IF NOT EXISTS DeviceConfigVersion (
|
VersionId TEXT PRIMARY KEY,
|
Status TEXT NOT NULL,
|
Hash TEXT NOT NULL DEFAULT '',
|
Author TEXT NOT NULL DEFAULT '',
|
Timestamp TEXT NOT NULL,
|
DiffDescription TEXT NOT NULL DEFAULT ''
|
);",
|
|
// 设备组模板表
|
@"CREATE TABLE IF NOT EXISTS DeviceGroupTemplate (
|
TemplateId TEXT PRIMARY KEY,
|
DeviceType TEXT NOT NULL DEFAULT '',
|
DriverType TEXT NOT NULL DEFAULT '',
|
Transport TEXT NOT NULL DEFAULT '',
|
DeviceCount INTEGER NOT NULL DEFAULT 0,
|
ChannelsPerDevice INTEGER NOT NULL DEFAULT 0,
|
AddressRule TEXT NOT NULL DEFAULT '',
|
SlotStart INTEGER NOT NULL DEFAULT 0
|
);"
|
};
|
|
helper.ExecuteBatchSql(sqls);
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"初始化设备配置数据库失败: {ex.Message}", ex);
|
}
|
}
|
|
#region DeviceDefinition CRUD
|
|
/// <summary>
|
/// 获取所有设备定义
|
/// </summary>
|
public List<DeviceDefinition> GetAllDevices()
|
{
|
try
|
{
|
using var helper = new SqliteHelper(_dbPath);
|
var dt = helper.GetDataTable("SELECT * FROM DeviceDefinition ORDER BY CreatedAt DESC;");
|
return MapToDeviceList(dt);
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"获取所有设备定义失败: {ex.Message}", ex);
|
}
|
}
|
|
/// <summary>
|
/// 根据ID获取设备定义
|
/// </summary>
|
public DeviceDefinition? GetDeviceById(string deviceId)
|
{
|
try
|
{
|
using var helper = new SqliteHelper(_dbPath);
|
var dt = helper.GetDataTable($"SELECT * FROM DeviceDefinition WHERE DeviceId = '{Escape(deviceId)}';");
|
var list = MapToDeviceList(dt);
|
return list.Count > 0 ? list[0] : null;
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"获取设备定义[{deviceId}]失败: {ex.Message}", ex);
|
}
|
}
|
|
/// <summary>
|
/// 保存设备定义（新增或更新）
|
/// </summary>
|
public void SaveDevice(DeviceDefinition device)
|
{
|
try
|
{
|
using var helper = new SqliteHelper(_dbPath);
|
device.UpdatedAt = DateTime.Now;
|
|
var sql = $@"INSERT OR REPLACE INTO DeviceDefinition
|
(DeviceId, Kind, DriverType, Transport, Endpoint, EffectiveVersion, CreatedAt, UpdatedAt)
|
VALUES (
|
'{Escape(device.DeviceId)}',
|
'{device.Kind}',
|
'{Escape(device.DriverType)}',
|
'{device.Transport}',
|
'{Escape(device.Endpoint)}',
|
'{Escape(device.EffectiveVersion)}',
|
'{device.CreatedAt:yyyy-MM-dd HH:mm:ss}',
|
'{device.UpdatedAt:yyyy-MM-dd HH:mm:ss}'
|
);";
|
helper.ExecuteSql(sql);
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"保存设备定义[{device.DeviceId}]失败: {ex.Message}", ex);
|
}
|
}
|
|
/// <summary>
|
/// 删除设备定义
|
/// </summary>
|
public void DeleteDevice(string deviceId)
|
{
|
try
|
{
|
using var helper = new SqliteHelper(_dbPath);
|
helper.ExecuteSql($"DELETE FROM DeviceDefinition WHERE DeviceId = '{Escape(deviceId)}';");
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"删除设备定义[{deviceId}]失败: {ex.Message}", ex);
|
}
|
}
|
|
private List<DeviceDefinition> MapToDeviceList(DataTable dt)
|
{
|
var list = new List<DeviceDefinition>();
|
foreach (DataRow row in dt.Rows)
|
{
|
list.Add(new DeviceDefinition
|
{
|
DeviceId = row["DeviceId"].ToString() ?? string.Empty,
|
Kind = Enum.TryParse<DeviceKind>(row["Kind"].ToString(), out var kind) ? kind : DeviceKind.PowerSupply,
|
DriverType = row["DriverType"].ToString() ?? string.Empty,
|
Transport = Enum.TryParse<TransportType>(row["Transport"].ToString(), out var tp) ? tp : TransportType.Tcp,
|
Endpoint = row["Endpoint"].ToString() ?? string.Empty,
|
EffectiveVersion = row["EffectiveVersion"].ToString() ?? string.Empty,
|
CreatedAt = DateTime.TryParse(row["CreatedAt"].ToString(), out var ca) ? ca : DateTime.Now,
|
UpdatedAt = DateTime.TryParse(row["UpdatedAt"].ToString(), out var ua) ? ua : DateTime.Now,
|
});
|
}
|
return list;
|
}
|
|
#endregion
|
|
#region DeviceChannel CRUD
|
|
/// <summary>
|
/// 获取指定设备的所有通道
|
/// </summary>
|
public List<DeviceChannel> GetChannelsByDevice(string deviceId)
|
{
|
try
|
{
|
using var helper = new SqliteHelper(_dbPath);
|
var dt = helper.GetDataTable($"SELECT * FROM DeviceChannel WHERE DeviceId = '{Escape(deviceId)}' ORDER BY ChannelId;");
|
return MapToChannelList(dt);
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"获取设备[{deviceId}]通道失败: {ex.Message}", ex);
|
}
|
}
|
|
/// <summary>
|
/// 保存设备通道（新增或更新）
|
/// </summary>
|
public void SaveChannel(DeviceChannel channel)
|
{
|
try
|
{
|
using var helper = new SqliteHelper(_dbPath);
|
var sql = $@"INSERT OR REPLACE INTO DeviceChannel
|
(DeviceId, ChannelId, Capability, MaxVoltage, MaxCurrent, Limits, SessionKey)
|
VALUES (
|
'{Escape(channel.DeviceId)}',
|
{channel.ChannelId},
|
'{Escape(channel.Capability)}',
|
{channel.MaxVoltage},
|
{channel.MaxCurrent},
|
'{Escape(channel.Limits)}',
|
'{Escape(channel.SessionKey)}'
|
);";
|
helper.ExecuteSql(sql);
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"保存通道[{channel.DeviceId}:{channel.ChannelId}]失败: {ex.Message}", ex);
|
}
|
}
|
|
/// <summary>
|
/// 删除设备通道
|
/// </summary>
|
public void DeleteChannel(string deviceId, int channelId)
|
{
|
try
|
{
|
using var helper = new SqliteHelper(_dbPath);
|
helper.ExecuteSql($"DELETE FROM DeviceChannel WHERE DeviceId = '{Escape(deviceId)}' AND ChannelId = {channelId};");
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"删除通道[{deviceId}:{channelId}]失败: {ex.Message}", ex);
|
}
|
}
|
|
private List<DeviceChannel> MapToChannelList(DataTable dt)
|
{
|
var list = new List<DeviceChannel>();
|
foreach (DataRow row in dt.Rows)
|
{
|
list.Add(new DeviceChannel
|
{
|
DeviceId = row["DeviceId"].ToString() ?? string.Empty,
|
ChannelId = Convert.ToInt32(row["ChannelId"]),
|
Capability = row["Capability"].ToString() ?? string.Empty,
|
MaxVoltage = Convert.ToDouble(row["MaxVoltage"]),
|
MaxCurrent = Convert.ToDouble(row["MaxCurrent"]),
|
Limits = row["Limits"].ToString() ?? "{}",
|
SessionKey = row["SessionKey"].ToString() ?? string.Empty,
|
});
|
}
|
return list;
|
}
|
|
#endregion
|
|
#region SlotDeviceMapping CRUD
|
|
/// <summary>
|
/// 获取所有Slot映射
|
/// </summary>
|
public List<SlotDeviceMapping> GetSlotMappings()
|
{
|
try
|
{
|
using var helper = new SqliteHelper(_dbPath);
|
var dt = helper.GetDataTable("SELECT * FROM SlotDeviceMapping ORDER BY SlotId;");
|
return MapToSlotMappingList(dt);
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"获取Slot映射失败: {ex.Message}", ex);
|
}
|
}
|
|
/// <summary>
|
/// 保存Slot映射（新增或更新）
|
/// </summary>
|
public void SaveSlotMapping(SlotDeviceMapping mapping)
|
{
|
try
|
{
|
using var helper = new SqliteHelper(_dbPath);
|
var sql = $@"INSERT OR REPLACE INTO SlotDeviceMapping
|
(SlotId, Capability, Version, DeviceId, ChannelId, MappingVersion)
|
VALUES (
|
'{Escape(mapping.SlotId)}',
|
'{Escape(mapping.Capability)}',
|
'{Escape(mapping.Version)}',
|
'{Escape(mapping.DeviceId)}',
|
{mapping.ChannelId},
|
'{Escape(mapping.MappingVersion)}'
|
);";
|
helper.ExecuteSql(sql);
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"保存Slot映射[{mapping.SlotId}]失败: {ex.Message}", ex);
|
}
|
}
|
|
/// <summary>
|
/// 删除Slot映射
|
/// </summary>
|
public void DeleteSlotMapping(string slotId, string capability, string version)
|
{
|
try
|
{
|
using var helper = new SqliteHelper(_dbPath);
|
helper.ExecuteSql($@"DELETE FROM SlotDeviceMapping
|
WHERE SlotId = '{Escape(slotId)}'
|
AND Capability = '{Escape(capability)}'
|
AND Version = '{Escape(version)}';");
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"删除Slot映射[{slotId}:{capability}:{version}]失败: {ex.Message}", ex);
|
}
|
}
|
|
private List<SlotDeviceMapping> MapToSlotMappingList(DataTable dt)
|
{
|
var list = new List<SlotDeviceMapping>();
|
foreach (DataRow row in dt.Rows)
|
{
|
list.Add(new SlotDeviceMapping
|
{
|
SlotId = row["SlotId"].ToString() ?? string.Empty,
|
Capability = row["Capability"].ToString() ?? string.Empty,
|
Version = row["Version"].ToString() ?? string.Empty,
|
DeviceId = row["DeviceId"].ToString() ?? string.Empty,
|
ChannelId = Convert.ToInt32(row["ChannelId"]),
|
MappingVersion = row["MappingVersion"].ToString() ?? string.Empty,
|
});
|
}
|
return list;
|
}
|
|
#endregion
|
|
#region DeviceConfigVersion CRUD
|
|
/// <summary>
|
/// 获取所有配置版本
|
/// </summary>
|
public List<DeviceConfigVersion> GetConfigVersions()
|
{
|
try
|
{
|
using var helper = new SqliteHelper(_dbPath);
|
var dt = helper.GetDataTable("SELECT * FROM DeviceConfigVersion ORDER BY Timestamp DESC;");
|
return MapToVersionList(dt);
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"获取配置版本失败: {ex.Message}", ex);
|
}
|
}
|
|
/// <summary>
|
/// 保存配置版本（新增或更新）
|
/// </summary>
|
public void SaveConfigVersion(DeviceConfigVersion version)
|
{
|
try
|
{
|
using var helper = new SqliteHelper(_dbPath);
|
var sql = $@"INSERT OR REPLACE INTO DeviceConfigVersion
|
(VersionId, Status, Hash, Author, Timestamp, DiffDescription)
|
VALUES (
|
'{Escape(version.VersionId)}',
|
'{version.Status}',
|
'{Escape(version.Hash)}',
|
'{Escape(version.Author)}',
|
'{version.Timestamp:yyyy-MM-dd HH:mm:ss}',
|
'{Escape(version.DiffDescription)}'
|
);";
|
helper.ExecuteSql(sql);
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"保存配置版本[{version.VersionId}]失败: {ex.Message}", ex);
|
}
|
}
|
|
/// <summary>
|
/// 更新版本状态
|
/// </summary>
|
public void UpdateVersionStatus(string versionId, string newStatus)
|
{
|
try
|
{
|
using var helper = new SqliteHelper(_dbPath);
|
helper.ExecuteSql($@"UPDATE DeviceConfigVersion
|
SET Status = '{Escape(newStatus)}'
|
WHERE VersionId = '{Escape(versionId)}';");
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"更新版本状态[{versionId}]失败: {ex.Message}", ex);
|
}
|
}
|
|
private List<DeviceConfigVersion> MapToVersionList(DataTable dt)
|
{
|
var list = new List<DeviceConfigVersion>();
|
foreach (DataRow row in dt.Rows)
|
{
|
list.Add(new DeviceConfigVersion
|
{
|
VersionId = row["VersionId"].ToString() ?? string.Empty,
|
Status = Enum.TryParse<VersionStatus>(row["Status"].ToString(), out var st) ? st : VersionStatus.Draft,
|
Hash = row["Hash"].ToString() ?? string.Empty,
|
Author = row["Author"].ToString() ?? string.Empty,
|
Timestamp = DateTime.TryParse(row["Timestamp"].ToString(), out var ts) ? ts : DateTime.Now,
|
DiffDescription = row["DiffDescription"].ToString() ?? string.Empty,
|
});
|
}
|
return list;
|
}
|
|
#endregion
|
|
#region DeviceGroupTemplate CRUD
|
|
/// <summary>
|
/// 获取所有设备组模板
|
/// </summary>
|
public List<DeviceGroupTemplate> GetGroupTemplates()
|
{
|
try
|
{
|
using var helper = new SqliteHelper(_dbPath);
|
var dt = helper.GetDataTable("SELECT * FROM DeviceGroupTemplate ORDER BY DeviceType;");
|
return MapToTemplateList(dt);
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"获取设备组模板失败: {ex.Message}", ex);
|
}
|
}
|
|
/// <summary>
|
/// 保存设备组模板（新增或更新）
|
/// </summary>
|
public void SaveGroupTemplate(DeviceGroupTemplate template)
|
{
|
try
|
{
|
using var helper = new SqliteHelper(_dbPath);
|
var sql = $@"INSERT OR REPLACE INTO DeviceGroupTemplate
|
(TemplateId, DeviceType, DriverType, Transport, DeviceCount, ChannelsPerDevice, AddressRule, SlotStart)
|
VALUES (
|
'{Escape(template.TemplateId)}',
|
'{Escape(template.DeviceType)}',
|
'{Escape(template.DriverType)}',
|
'{Escape(template.Transport)}',
|
{template.DeviceCount},
|
{template.ChannelsPerDevice},
|
'{Escape(template.AddressRule)}',
|
{template.SlotStart}
|
);";
|
helper.ExecuteSql(sql);
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"保存设备组模板[{template.TemplateId}]失败: {ex.Message}", ex);
|
}
|
}
|
|
private List<DeviceGroupTemplate> MapToTemplateList(DataTable dt)
|
{
|
var list = new List<DeviceGroupTemplate>();
|
foreach (DataRow row in dt.Rows)
|
{
|
list.Add(new DeviceGroupTemplate
|
{
|
TemplateId = row["TemplateId"].ToString() ?? string.Empty,
|
DeviceType = row["DeviceType"].ToString() ?? string.Empty,
|
DriverType = row["DriverType"].ToString() ?? string.Empty,
|
Transport = row["Transport"].ToString() ?? string.Empty,
|
DeviceCount = Convert.ToInt32(row["DeviceCount"]),
|
ChannelsPerDevice = Convert.ToInt32(row["ChannelsPerDevice"]),
|
AddressRule = row["AddressRule"].ToString() ?? string.Empty,
|
SlotStart = Convert.ToInt32(row["SlotStart"]),
|
});
|
}
|
return list;
|
}
|
|
#endregion
|
|
#region 冲突检测与哈希计算
|
|
/// <summary>
|
/// 检测配置冲突：IP冲突、Slot映射重复、通道越界等
|
/// </summary>
|
/// <returns>冲突描述列表，为空则表示无冲突</returns>
|
public List<string> DetectConflicts()
|
{
|
var conflicts = new List<string>();
|
try
|
{
|
using var helper = new SqliteHelper(_dbPath);
|
|
// 1. 检测IP/Endpoint冲突（相同Endpoint的不同设备）
|
var ipSql = @"SELECT Endpoint, COUNT(*) as Cnt
|
FROM DeviceDefinition
|
WHERE Transport = 'Tcp'
|
GROUP BY Endpoint
|
HAVING Cnt > 1;";
|
var ipDt = helper.GetDataTable(ipSql);
|
foreach (DataRow row in ipDt.Rows)
|
{
|
conflicts.Add($"IP/Endpoint冲突: {row["Endpoint"]} 被 {row["Cnt"]} 个设备使用");
|
}
|
|
// 2. 检测Slot映射重复（相同Slot+Capability+Version映射到不同设备）
|
var slotSql = @"SELECT SlotId, Capability, Version, COUNT(*) as Cnt
|
FROM SlotDeviceMapping
|
GROUP BY SlotId, Capability, Version
|
HAVING Cnt > 1;";
|
var slotDt = helper.GetDataTable(slotSql);
|
foreach (DataRow row in slotDt.Rows)
|
{
|
conflicts.Add($"Slot映射重复: Slot={row["SlotId"]}, Capability={row["Capability"]}, Version={row["Version"]}");
|
}
|
|
// 3. 检测通道越界（MaxVoltage或MaxCurrent为负数）
|
var channelSql = @"SELECT DeviceId, ChannelId, MaxVoltage, MaxCurrent
|
FROM DeviceChannel
|
WHERE MaxVoltage < 0 OR MaxCurrent < 0;";
|
var channelDt = helper.GetDataTable(channelSql);
|
foreach (DataRow row in channelDt.Rows)
|
{
|
conflicts.Add($"通道参数越界: 设备={row["DeviceId"]}, 通道={row["ChannelId"]}, " +
|
$"电压={row["MaxVoltage"]}, 电流={row["MaxCurrent"]}");
|
}
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"冲突检测失败: {ex.Message}", ex);
|
}
|
|
return conflicts;
|
}
|
|
/// <summary>
|
/// 计算当前配置的SHA256哈希
|
/// </summary>
|
/// <returns>SHA256哈希字符串（小写十六进制）</returns>
|
public string ComputeConfigHash()
|
{
|
try
|
{
|
using var helper = new SqliteHelper(_dbPath);
|
|
// 拼接所有设备定义的关键信息作为哈希输入
|
var sb = new StringBuilder();
|
|
var deviceDt = helper.GetDataTable("SELECT * FROM DeviceDefinition ORDER BY DeviceId;");
|
foreach (DataRow row in deviceDt.Rows)
|
{
|
sb.Append($"DEVICE:{row["DeviceId"]}|{row["Kind"]}|{row["DriverType"]}|{row["Transport"]}|{row["Endpoint"]};");
|
}
|
|
var channelDt = helper.GetDataTable("SELECT * FROM DeviceChannel ORDER BY DeviceId, ChannelId;");
|
foreach (DataRow row in channelDt.Rows)
|
{
|
sb.Append($"CHANNEL:{row["DeviceId"]}|{row["ChannelId"]}|{row["Capability"]}|{row["MaxVoltage"]}|{row["MaxCurrent"]};");
|
}
|
|
var slotDt = helper.GetDataTable("SELECT * FROM SlotDeviceMapping ORDER BY SlotId, Capability, Version;");
|
foreach (DataRow row in slotDt.Rows)
|
{
|
sb.Append($"SLOT:{row["SlotId"]}|{row["Capability"]}|{row["Version"]}|{row["DeviceId"]}|{row["ChannelId"]};");
|
}
|
|
using var sha256 = SHA256.Create();
|
var hashBytes = sha256.ComputeHash(Encoding.UTF8.GetBytes(sb.ToString()));
|
return BitConverter.ToString(hashBytes).Replace("-", "").ToLowerInvariant();
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"计算配置哈希失败: {ex.Message}", ex);
|
}
|
}
|
|
#endregion
|
|
#region 辅助方法
|
|
/// <summary>
|
/// SQL字符串转义（防止注入）
|
/// </summary>
|
private static string Escape(string value)
|
{
|
return value?.Replace("'", "''") ?? string.Empty;
|
}
|
|
#endregion
|
}
|
}
|
