From e57938fbda9bff702004038f76d009422968bff5 Mon Sep 17 00:00:00 2001
From: alielie <alielie@git.com>
Date: 星期五, 06 十一月 2026 18:05:07 +0800
Subject: [PATCH] 提交代码
---
DeviceService/DeviceManager/DeviceDatabase.cs | 328 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 328 insertions(+), 0 deletions(-)
diff --git a/DeviceService/DeviceManager/DeviceDatabase.cs b/DeviceService/DeviceManager/DeviceDatabase.cs
new file mode 100644
index 0000000..08270e4
--- /dev/null
+++ b/DeviceService/DeviceManager/DeviceDatabase.cs
@@ -0,0 +1,328 @@
+using DeviceService.Core;
+using DeviceService.Logging;
+using System.Data.SQLite;
+using System.IO;
+
+namespace DeviceService.Device
+{
+ /// <summary>璁惧璁板綍锛圖evice 琛ㄧ殑涓�琛岋級銆�</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锛欼d 涓鸿嚜澧炰富閿紝DeviceId 涓哄敮涓�涓氬姟鏍囪瘑锛孴ype 鐢ㄤ簬瀹氫綅璁惧瀹炵幇绫�
+ ///
+ /// 鎵�鏈夋搷浣滃け璐ヤ粎閫氳繃 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銆丏eviceId 鎺掑簭锛夈��</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>鏃у簱锛圖eviceId 浣滀富閿級鍗囩骇涓� 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);
+ }
+ }
+}
--
Gitblit v1.9.1