using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.IO;
|
using System.Linq;
|
using HMI.Core.Util;
|
using log4net;
|
using Plugin.DeviceDebug.Entity;
|
|
namespace Plugin.DeviceDebug.Model
|
{
|
/// <summary>
|
/// 数据访问层 - 管理调试会话和操作审计的持久化
|
/// </summary>
|
public class DeviceDebugModel
|
{
|
private static readonly ILog log = LogManager.GetLogger(typeof(DeviceDebugModel));
|
private SqliteHelper? _sqliteHelper;
|
private string _dbPath = string.Empty;
|
|
/// <summary>
|
/// 初始化数据库，创建必要的表结构
|
/// </summary>
|
public void InitDatabase(string dbPath)
|
{
|
try
|
{
|
_dbPath = dbPath;
|
|
// 确保目录存在
|
string? dir = Path.GetDirectoryName(dbPath);
|
if (!string.IsNullOrEmpty(dir) && !Directory.Exists(dir))
|
{
|
Directory.CreateDirectory(dir);
|
}
|
|
// 如果数据库文件不存在，创建它
|
if (!File.Exists(dbPath))
|
{
|
SqliteHelper.CreateDatabase(dbPath);
|
log.Info($"创建调试数据库: {dbPath}");
|
}
|
|
_sqliteHelper = new SqliteHelper(dbPath);
|
|
// 创建 debug_session 表
|
string createSessionTable = @"
|
CREATE TABLE IF NOT EXISTS debug_session (
|
session_id TEXT PRIMARY KEY,
|
device_id TEXT NOT NULL,
|
operator_id TEXT NOT NULL,
|
operator_name TEXT,
|
effective_version TEXT,
|
state INTEGER NOT NULL DEFAULT 0,
|
reservation_ref TEXT,
|
created_at TEXT NOT NULL,
|
closed_at TEXT
|
)";
|
|
// 创建 debug_operation 表
|
string createOperationTable = @"
|
CREATE TABLE IF NOT EXISTS debug_operation (
|
operation_id TEXT PRIMARY KEY,
|
session_id TEXT NOT NULL,
|
command_type TEXT NOT NULL,
|
parameters TEXT,
|
outcome TEXT NOT NULL,
|
device_readback TEXT,
|
error_code TEXT,
|
error_message TEXT,
|
timestamp TEXT NOT NULL,
|
FOREIGN KEY (session_id) REFERENCES debug_session(session_id)
|
)";
|
|
_sqliteHelper.ExecuteSql(createSessionTable);
|
_sqliteHelper.ExecuteSql(createOperationTable);
|
|
log.Info("调试数据库表初始化完成");
|
}
|
catch (Exception ex)
|
{
|
log.Error($"初始化调试数据库失败: {ex.Message}", ex);
|
throw;
|
}
|
}
|
|
#region 会话 CRUD
|
|
/// <summary>
|
/// 创建会话
|
/// </summary>
|
public void CreateSession(DebugSession session)
|
{
|
try
|
{
|
if (_sqliteHelper == null)
|
throw new InvalidOperationException("数据库未初始化，请先调用 InitDatabase");
|
|
string sql = $@"
|
INSERT INTO debug_session (
|
session_id, device_id, operator_id, operator_name,
|
effective_version, state, reservation_ref, created_at, closed_at
|
) VALUES (
|
'{EscapeSql(session.SessionId)}',
|
'{EscapeSql(session.DeviceId)}',
|
'{EscapeSql(session.OperatorId)}',
|
'{EscapeSql(session.OperatorName)}',
|
'{EscapeSql(session.EffectiveVersion)}',
|
{(int)session.State},
|
'{EscapeSql(session.ReservationRef)}',
|
'{session.CreatedAt:yyyy-MM-dd HH:mm:ss}',
|
{(session.ClosedAt.HasValue ? $"'{session.ClosedAt.Value:yyyy-MM-dd HH:mm:ss}'" : "NULL")}
|
)";
|
|
_sqliteHelper.ExecuteSql(sql);
|
log.Debug($"创建会话: {session.SessionId}, 设备: {session.DeviceId}");
|
}
|
catch (Exception ex)
|
{
|
log.Error($"创建会话失败: {ex.Message}", ex);
|
throw;
|
}
|
}
|
|
/// <summary>
|
/// 获取会话
|
/// </summary>
|
public DebugSession? GetSession(string sessionId)
|
{
|
try
|
{
|
if (_sqliteHelper == null)
|
throw new InvalidOperationException("数据库未初始化，请先调用 InitDatabase");
|
|
string sql = $"SELECT * FROM debug_session WHERE session_id = '{EscapeSql(sessionId)}'";
|
DataTable dt = _sqliteHelper.GetDataTable(sql);
|
|
if (dt.Rows.Count == 0)
|
return null;
|
|
return MapToDebugSession(dt.Rows[0]);
|
}
|
catch (Exception ex)
|
{
|
log.Error($"获取会话失败: {ex.Message}", ex);
|
return null;
|
}
|
}
|
|
/// <summary>
|
/// 更新会话
|
/// </summary>
|
public void UpdateSession(DebugSession session)
|
{
|
try
|
{
|
if (_sqliteHelper == null)
|
throw new InvalidOperationException("数据库未初始化，请先调用 InitDatabase");
|
|
string sql = $@"
|
UPDATE debug_session SET
|
device_id = '{EscapeSql(session.DeviceId)}',
|
operator_id = '{EscapeSql(session.OperatorId)}',
|
operator_name = '{EscapeSql(session.OperatorName)}',
|
effective_version = '{EscapeSql(session.EffectiveVersion)}',
|
state = {(int)session.State},
|
reservation_ref = '{EscapeSql(session.ReservationRef)}',
|
closed_at = {(session.ClosedAt.HasValue ? $"'{session.ClosedAt.Value:yyyy-MM-dd HH:mm:ss}'" : "NULL")}
|
WHERE session_id = '{EscapeSql(session.SessionId)}'";
|
|
_sqliteHelper.ExecuteSql(sql);
|
log.Debug($"更新会话: {session.SessionId}, 状态: {session.State}");
|
}
|
catch (Exception ex)
|
{
|
log.Error($"更新会话失败: {ex.Message}", ex);
|
throw;
|
}
|
}
|
|
/// <summary>
|
/// 获取设备的所有活跃会话（未关闭的）
|
/// </summary>
|
public List<DebugSession> GetActiveSessionsByDevice(string deviceId)
|
{
|
try
|
{
|
if (_sqliteHelper == null)
|
throw new InvalidOperationException("数据库未初始化，请先调用 InitDatabase");
|
|
string sql = $@"
|
SELECT * FROM debug_session
|
WHERE device_id = '{EscapeSql(deviceId)}'
|
AND state != {(int)DebugSessionState.Closed}
|
ORDER BY created_at DESC";
|
|
DataTable dt = _sqliteHelper.GetDataTable(sql);
|
return dt.Rows.Cast<DataRow>().Select(MapToDebugSession).ToList();
|
}
|
catch (Exception ex)
|
{
|
log.Error($"获取设备活跃会话失败: {ex.Message}", ex);
|
return new List<DebugSession>();
|
}
|
}
|
|
/// <summary>
|
/// 获取所有会话
|
/// </summary>
|
public List<DebugSession> GetAllSessions()
|
{
|
try
|
{
|
if (_sqliteHelper == null)
|
throw new InvalidOperationException("数据库未初始化，请先调用 InitDatabase");
|
|
string sql = "SELECT * FROM debug_session ORDER BY created_at DESC";
|
DataTable dt = _sqliteHelper.GetDataTable(sql);
|
return dt.Rows.Cast<DataRow>().Select(MapToDebugSession).ToList();
|
}
|
catch (Exception ex)
|
{
|
log.Error($"获取所有会话失败: {ex.Message}", ex);
|
return new List<DebugSession>();
|
}
|
}
|
|
#endregion
|
|
#region 审计 CRUD
|
|
/// <summary>
|
/// 记录操作
|
/// </summary>
|
public void RecordOperation(DebugOperation operation)
|
{
|
try
|
{
|
if (_sqliteHelper == null)
|
throw new InvalidOperationException("数据库未初始化，请先调用 InitDatabase");
|
|
string sql = $@"
|
INSERT INTO debug_operation (
|
operation_id, session_id, command_type, parameters,
|
outcome, device_readback, error_code, error_message, timestamp
|
) VALUES (
|
'{EscapeSql(operation.OperationId)}',
|
'{EscapeSql(operation.SessionId)}',
|
'{EscapeSql(operation.CommandType)}',
|
'{EscapeSql(operation.Parameters)}',
|
'{EscapeSql(operation.Outcome)}',
|
'{EscapeSql(operation.DeviceReadback)}',
|
'{EscapeSql(operation.ErrorCode)}',
|
'{EscapeSql(operation.ErrorMessage)}',
|
'{operation.Timestamp:yyyy-MM-dd HH:mm:ss}'
|
)";
|
|
_sqliteHelper.ExecuteSql(sql);
|
log.Debug($"记录操作: {operation.OperationId}, 类型: {operation.CommandType}, 结果: {operation.Outcome}");
|
}
|
catch (Exception ex)
|
{
|
log.Error($"记录操作失败: {ex.Message}", ex);
|
throw;
|
}
|
}
|
|
/// <summary>
|
/// 获取会话的所有操作
|
/// </summary>
|
public List<DebugOperation> GetOperationsBySession(string sessionId)
|
{
|
try
|
{
|
if (_sqliteHelper == null)
|
throw new InvalidOperationException("数据库未初始化，请先调用 InitDatabase");
|
|
string sql = $@"
|
SELECT * FROM debug_operation
|
WHERE session_id = '{EscapeSql(sessionId)}'
|
ORDER BY timestamp DESC";
|
|
DataTable dt = _sqliteHelper.GetDataTable(sql);
|
return dt.Rows.Cast<DataRow>().Select(MapToDebugOperation).ToList();
|
}
|
catch (Exception ex)
|
{
|
log.Error($"获取会话操作失败: {ex.Message}", ex);
|
return new List<DebugOperation>();
|
}
|
}
|
|
/// <summary>
|
/// 获取设备最近的操作
|
/// </summary>
|
public List<DebugOperation> GetRecentOperations(string deviceId, int count)
|
{
|
try
|
{
|
if (_sqliteHelper == null)
|
throw new InvalidOperationException("数据库未初始化，请先调用 InitDatabase");
|
|
string sql = $@"
|
SELECT o.* FROM debug_operation o
|
INNER JOIN debug_session s ON o.session_id = s.session_id
|
WHERE s.device_id = '{EscapeSql(deviceId)}'
|
ORDER BY o.timestamp DESC
|
LIMIT {count}";
|
|
DataTable dt = _sqliteHelper.GetDataTable(sql);
|
return dt.Rows.Cast<DataRow>().Select(MapToDebugOperation).ToList();
|
}
|
catch (Exception ex)
|
{
|
log.Error($"获取最近操作失败: {ex.Message}", ex);
|
return new List<DebugOperation>();
|
}
|
}
|
|
#endregion
|
|
#region 辅助方法
|
|
/// <summary>
|
/// 将 DataRow 映射为 DebugSession
|
/// </summary>
|
private DebugSession MapToDebugSession(DataRow row)
|
{
|
return new DebugSession
|
{
|
SessionId = row["session_id"].ToString() ?? string.Empty,
|
DeviceId = row["device_id"].ToString() ?? string.Empty,
|
OperatorId = row["operator_id"].ToString() ?? string.Empty,
|
OperatorName = row["operator_name"]?.ToString() ?? string.Empty,
|
EffectiveVersion = row["effective_version"]?.ToString() ?? string.Empty,
|
State = (DebugSessionState)Convert.ToInt32(row["state"]),
|
ReservationRef = row["reservation_ref"]?.ToString() ?? string.Empty,
|
CreatedAt = DateTime.Parse(row["created_at"].ToString() ?? DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")),
|
ClosedAt = row["closed_at"] != DBNull.Value && !string.IsNullOrEmpty(row["closed_at"].ToString())
|
? DateTime.Parse(row["closed_at"].ToString()!)
|
: null
|
};
|
}
|
|
/// <summary>
|
/// 将 DataRow 映射为 DebugOperation
|
/// </summary>
|
private DebugOperation MapToDebugOperation(DataRow row)
|
{
|
return new DebugOperation
|
{
|
OperationId = row["operation_id"].ToString() ?? string.Empty,
|
SessionId = row["session_id"].ToString() ?? string.Empty,
|
CommandType = row["command_type"].ToString() ?? string.Empty,
|
Parameters = row["parameters"]?.ToString() ?? string.Empty,
|
Outcome = row["outcome"].ToString() ?? string.Empty,
|
DeviceReadback = row["device_readback"]?.ToString() ?? string.Empty,
|
ErrorCode = row["error_code"]?.ToString() ?? string.Empty,
|
ErrorMessage = row["error_message"]?.ToString() ?? string.Empty,
|
Timestamp = DateTime.Parse(row["timestamp"].ToString() ?? DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
|
};
|
}
|
|
/// <summary>
|
/// SQL 字符串转义（防止注入）
|
/// </summary>
|
private string EscapeSql(string value)
|
{
|
return value.Replace("'", "''");
|
}
|
|
#endregion
|
}
|
}
|
