using DeviceService.Core;
|
using DeviceService.Logging;
|
using System.Data.SQLite;
|
using System.IO;
|
|
namespace DeviceService.Device
|
{
|
/// <summary>设备记录（Device 表的一行）。</summary>
|
public sealed class DeviceRecord
|
{
|
/// <summary>自增主键，对应设备实例的 Id；0 表示尚未入库。</summary>
|
public long Id { get; set; }
|
|
/// <summary>设备业务标识（唯一），对应设备实例的 DeviceId。</summary>
|
public string DeviceId { get; set; } = string.Empty;
|
|
public string Type { get; set; } = string.Empty;
|
|
public string Brand { get; set; } = string.Empty;
|
|
public string ConnectStr { get; set; } = string.Empty;
|
|
public AccessType AccessType { get; set; }
|
}
|
|
/// <summary>
|
/// 设备信息 SQLite 持久化：数据库文件位于 应用根目录\Config\Devices.db。
|
///
|
/// 表结构：
|
/// - Device：Id 为自增主键，DeviceId 为唯一业务标识，Type 用于定位设备实现类
|
///
|
/// 所有操作失败仅通过 log 上报，不会中断设备加载流程。
|
/// </summary>
|
public sealed class DeviceDatabase
|
{
|
private const string CreateTableSql = @"
|
CREATE TABLE IF NOT EXISTS Device (
|
Id INTEGER PRIMARY KEY AUTOINCREMENT,
|
DeviceId TEXT NOT NULL,
|
Type TEXT NOT NULL,
|
Brand TEXT,
|
ConnectStr TEXT,
|
AccessType INTEGER NOT NULL DEFAULT 0
|
);";
|
|
private static readonly object _staticSync = new object();
|
private static DeviceDatabase? _instance;
|
|
public static DeviceDatabase Instance
|
{
|
get
|
{
|
lock (_staticSync)
|
{
|
return _instance ??= new DeviceDatabase();
|
}
|
}
|
}
|
|
/// <summary>配置目录：应用根目录\Config。</summary>
|
public static string ConfigDirectory =>
|
Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Config");
|
|
/// <summary>数据库文件路径。</summary>
|
public static string DatabasePath =>
|
Path.Combine(ConfigDirectory, "Devices.db");
|
|
private readonly object _sync = new object();
|
private bool _initialized;
|
|
private DeviceDatabase()
|
{
|
}
|
|
/// <summary>确保 Config 目录与库表结构存在。失败返回 false 并通过 log 上报。</summary>
|
private bool EnsureReady()
|
{
|
if (_initialized)
|
{
|
return true;
|
}
|
|
try
|
{
|
if (!Directory.Exists(ConfigDirectory))
|
{
|
Directory.CreateDirectory(ConfigDirectory);
|
}
|
|
using (var conn = Open())
|
{
|
ExecuteNonQuery(conn, CreateTableSql, null);
|
|
// 旧库的结构是 DeviceId 主键，升级为 Id 自增主键
|
MigrateToAutoIncrementId(conn);
|
|
ExecuteNonQuery(conn,
|
"CREATE UNIQUE INDEX IF NOT EXISTS IX_Device_DeviceId ON Device(DeviceId);",
|
null);
|
}
|
|
_initialized = true;
|
return true;
|
}
|
catch (Exception ex)
|
{
|
GlobalLogger.Logger.Error("DeviceManager", $"[设备库] 初始化失败（{DatabasePath}）: {ex.Message}");
|
return false;
|
}
|
}
|
|
private SQLiteConnection Open()
|
{
|
var conn = new SQLiteConnection($"Data Source={DatabasePath};Version=3;");
|
conn.Open();
|
return conn;
|
}
|
|
/// <summary>读取库中全部设备记录（按 Type、DeviceId 排序）。</summary>
|
public IReadOnlyList<DeviceRecord> LoadAll()
|
{
|
var result = new List<DeviceRecord>();
|
if (!EnsureReady())
|
{
|
return result;
|
}
|
|
try
|
{
|
lock (_sync)
|
{
|
using (var conn = Open())
|
using (var cmd = conn.CreateCommand())
|
{
|
cmd.CommandText =
|
"SELECT * FROM Device ORDER BY Type, DeviceId";
|
using (var reader = cmd.ExecuteReader())
|
{
|
while (reader.Read())
|
{
|
result.Add(new DeviceRecord
|
{
|
Id = GetInt64(reader, "Id"),
|
DeviceId = GetString(reader, "DeviceId"),
|
Type = GetString(reader, "Type"),
|
Brand = GetString(reader, "Brand"),
|
ConnectStr = GetString(reader, "ConnectStr"),
|
AccessType = (AccessType)GetInt32(reader, "AccessType")
|
});
|
}
|
}
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
GlobalLogger.Logger.Error("DeviceManager", $"[设备库] 读取设备列表失败: {ex.Message}");
|
}
|
|
return result;
|
}
|
|
/// <summary>
|
/// 写入（覆盖）一台设备的记录，返回其自增主键 Id；失败返回 0。
|
/// Id 为 0 时新增，否则按 Id 更新。
|
/// </summary>
|
public long Save(DeviceRecord record, Action<string>? log = null)
|
{
|
if (record == null || string.IsNullOrWhiteSpace(record.DeviceId))
|
{
|
return 0;
|
}
|
|
if (!EnsureReady())
|
{
|
return 0;
|
}
|
|
try
|
{
|
lock (_sync)
|
{
|
using (var conn = Open())
|
{
|
if (record.Id > 0)
|
{
|
int affected;
|
using (var cmd = conn.CreateCommand())
|
{
|
cmd.CommandText = @"
|
UPDATE Device SET DeviceId = @id, Type = @t, Brand = @b, ConnectStr = @c, AccessType = @a
|
WHERE Id = @pk";
|
cmd.Parameters.AddWithValue("@id", record.DeviceId);
|
cmd.Parameters.AddWithValue("@t", record.Type ?? string.Empty);
|
cmd.Parameters.AddWithValue("@b", record.Brand ?? string.Empty);
|
cmd.Parameters.AddWithValue("@c", record.ConnectStr ?? string.Empty);
|
cmd.Parameters.AddWithValue("@a", (int)record.AccessType);
|
cmd.Parameters.AddWithValue("@pk", record.Id);
|
affected = cmd.ExecuteNonQuery();
|
}
|
|
if (affected > 0)
|
{
|
return record.Id;
|
}
|
}
|
|
using (var cmd = conn.CreateCommand())
|
{
|
cmd.CommandText = @"
|
INSERT INTO Device(DeviceId, Type, Brand, ConnectStr, AccessType)
|
VALUES(@id, @t, @b, @c, @a)";
|
cmd.Parameters.AddWithValue("@id", record.DeviceId);
|
cmd.Parameters.AddWithValue("@t", record.Type ?? string.Empty);
|
cmd.Parameters.AddWithValue("@b", record.Brand ?? string.Empty);
|
cmd.Parameters.AddWithValue("@c", record.ConnectStr ?? string.Empty);
|
cmd.Parameters.AddWithValue("@a", (int)record.AccessType);
|
cmd.ExecuteNonQuery();
|
}
|
|
using (var cmd = conn.CreateCommand())
|
{
|
cmd.CommandText = "SELECT last_insert_rowid()";
|
return Convert.ToInt64(cmd.ExecuteScalar());
|
}
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
log?.Invoke($"[设备库] 保存 {record.DeviceId} 失败: {ex.Message}");
|
return 0;
|
}
|
}
|
|
/// <summary>删除一台设备的记录。</summary>
|
public void Remove(string deviceId)
|
{
|
if (string.IsNullOrEmpty(deviceId) || !EnsureReady())
|
{
|
return;
|
}
|
|
try
|
{
|
lock (_sync)
|
{
|
using (var conn = Open())
|
using (var cmd = conn.CreateCommand())
|
{
|
cmd.CommandText = "DELETE FROM Device WHERE DeviceId = @id";
|
cmd.Parameters.AddWithValue("@id", deviceId);
|
cmd.ExecuteNonQuery();
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
GlobalLogger.Logger.Error("DeviceManager", $"[设备库] 移除 {deviceId} 失败: {ex.Message}");
|
}
|
}
|
|
/// <summary>旧库（DeviceId 作主键）升级为 Id 自增主键。</summary>
|
private static void MigrateToAutoIncrementId(SQLiteConnection conn)
|
{
|
var columns = new List<string>();
|
using (var cmd = conn.CreateCommand())
|
{
|
cmd.CommandText = "PRAGMA table_info(Device)";
|
using (var reader = cmd.ExecuteReader())
|
{
|
while (reader.Read())
|
{
|
columns.Add(GetString(reader, "name"));
|
}
|
}
|
}
|
|
if (columns.Count == 0 || columns.Contains("Id"))
|
{
|
return;
|
}
|
|
lock (_staticSync)
|
{
|
using (var tx = conn.BeginTransaction())
|
{
|
ExecuteNonQuery(conn, "DROP TABLE IF EXISTS Device_old;", tx);
|
ExecuteNonQuery(conn, "ALTER TABLE Device RENAME TO Device_old;", tx);
|
ExecuteNonQuery(conn, CreateTableSql, tx);
|
ExecuteNonQuery(conn, @"
|
INSERT INTO Device(DeviceId, Type, Brand, ConnectStr, AccessType)
|
SELECT DeviceId, Type, Brand, ConnectStr, AccessType FROM Device_old;", tx);
|
ExecuteNonQuery(conn, "DROP TABLE Device_old;", tx);
|
tx.Commit();
|
}
|
}
|
}
|
|
private static void ExecuteNonQuery(SQLiteConnection conn, string sql, SQLiteTransaction? tx)
|
{
|
using (var cmd = conn.CreateCommand())
|
{
|
cmd.Transaction = tx;
|
cmd.CommandText = sql;
|
cmd.ExecuteNonQuery();
|
}
|
}
|
|
private static string GetString(SQLiteDataReader reader, string column)
|
{
|
object value = reader[column];
|
return value == DBNull.Value ? string.Empty : value.ToString() ?? string.Empty;
|
}
|
|
private static int GetInt32(SQLiteDataReader reader, string column)
|
{
|
object value = reader[column];
|
return value == DBNull.Value ? 0 : Convert.ToInt32(value);
|
}
|
|
private static long GetInt64(SQLiteDataReader reader, string column)
|
{
|
object value = reader[column];
|
return value == DBNull.Value ? 0L : Convert.ToInt64(value);
|
}
|
}
|
}
|
