优化数据库实体配置和序列管理

啊鑫

2025-07-22 f4ea081986ac90eceebe7e4a97e53ac1d4aa03d9

优化数据库实体配置和序列管理

- 修改所有实体属性为nullable（除ID外），提高数据灵活性
- 配置Oracle序列绑定到ID字段，支持自动主键生成
- 增强DatabaseManager支持序列自动创建
- 新增CreateSequences.sql手动脚本供参考
- 修复bool?类型比较问题，确保代码兼容性

兼容Oracle 11g Release 11.2.0.1.0

 Database/CreateSequences.sql

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,41 @@
-- Oracle 11g序列创建脚本
-- ç”¨äºŽGSModbus系统的实体ID自动生成
-- å…¼å®¹Oracle 11.2.0.1.0版本

-- åˆ›å»ºMODBUS_DATA表的序列
CREATE SEQUENCE SEQ_MODBUS_DATA_ID
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOCYCLE
CACHE 20;

-- åˆ›å»ºCOMMUNICATION_LOG表的序列
CREATE SEQUENCE SEQ_COMMUNICATION_LOG_ID
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOCYCLE
CACHE 20;

-- åˆ›å»ºERROR_LOG表的序列
CREATE SEQUENCE SEQ_ERROR_LOG_ID
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOCYCLE
CACHE 20;

-- åˆ›å»ºSTATISTICS表的序列
CREATE SEQUENCE SEQ_STATISTICS_ID
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOCYCLE
CACHE 20;

-- æŸ¥çœ‹å·²åˆ›å»ºçš„序列
SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CACHE_SIZE 
FROM USER_SEQUENCES 
WHERE SEQUENCE_NAME LIKE 'SEQ_%_ID'
ORDER BY SEQUENCE_NAME;

 Database/DatabaseEntities.cs

@@ -11,26 +11,26 @@
        /// <summary>
        /// ä¸»é”®ID
        /// </summary>
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        [SugarColumn(ColumnName = "ID", OracleSequenceName = "SEQ_MODBUS_DATA_ID", IsPrimaryKey = true)]
        public long Id { get; set; }

        /// <summary>
        /// æ•°æ®è¯»å–时间
        /// </summary>
        [SugarColumn(IsNullable = false, ColumnDescription = "数据读取时间")]
        public DateTime ReadTime { get; set; }
        [SugarColumn(IsNullable = true, ColumnDescription = "数据读取时间")]
        public DateTime? ReadTime { get; set; }

        /// <summary>
        /// é¡¹ç›®åç§°ï¼ˆæ¥è‡ªé…ç½®ï¼‰
        /// </summary>
        [SugarColumn(Length = 100, IsNullable = false, ColumnDescription = "项目名称")]
        public string ProjectName { get; set; } = string.Empty;
        [SugarColumn(Length = 100, IsNullable = true, ColumnDescription = "项目名称")]
        public string? ProjectName { get; set; } 

        /// <summary>
        /// PLC IP地址
        /// </summary>
        [SugarColumn(Length = 50, IsNullable = false, ColumnDescription = "PLC IP地址")]
        public string PlcIpAddress { get; set; } = string.Empty;
        [SugarColumn(Length = 50, IsNullable = true, ColumnDescription = "PLC IP地址")]
        public string? PlcIpAddress { get; set; } 

        /// <summary>
        /// æŽ§åˆ¶ä¿¡å·æ•°æ®ï¼ˆJSON格式）
@@ -59,8 +59,8 @@
        /// <summary>
        /// æ•°æ®åˆ›å»ºæ—¶é—´
        /// </summary>
        [SugarColumn(IsNullable = false, ColumnDescription = "记录创建时间")]
        public DateTime CreatedAt { get; set; } = DateTime.Now;
        [SugarColumn(IsNullable = true, ColumnDescription = "记录创建时间")]
        public DateTime? CreatedAt { get; set; } = DateTime.Now;
    }

    /// <summary>
@@ -72,32 +72,32 @@
        /// <summary>
        /// ä¸»é”®ID
        /// </summary>
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        [SugarColumn(ColumnName = "ID", OracleSequenceName = "SEQ_COMMUNICATION_LOG_ID", IsPrimaryKey = true)]
        public long Id { get; set; }

        /// <summary>
        /// æ—¥å¿—时间
        /// </summary>
        [SugarColumn(IsNullable = false, ColumnDescription = "日志时间")]
        public DateTime LogTime { get; set; }
        [SugarColumn(IsNullable = true, ColumnDescription = "日志时间")]
        public DateTime? LogTime { get; set; }

        /// <summary>
        /// é¡¹ç›®åç§°
        /// </summary>
        [SugarColumn(Length = 100, IsNullable = false, ColumnDescription = "项目名称")]
        public string ProjectName { get; set; } = string.Empty;
        [SugarColumn(Length = 100, IsNullable = true, ColumnDescription = "项目名称")]
        public string? ProjectName { get; set; }

        /// <summary>
        /// PLC IP地址
        /// </summary>
        [SugarColumn(Length = 50, IsNullable = false, ColumnDescription = "PLC IP地址")]
        public string PlcIpAddress { get; set; } = string.Empty;
        [SugarColumn(Length = 50, IsNullable = true, ColumnDescription = "PLC IP地址")]
        public string? PlcIpAddress { get; set; } 

        /// <summary>
        /// äº‹ä»¶ç±»åž‹ï¼ˆConnected, Disconnected, DataReceived, Error等）
        /// </summary>
        [SugarColumn(Length = 50, IsNullable = false, ColumnDescription = "事件类型")]
        public string EventType { get; set; } = string.Empty;
        [SugarColumn(Length = 50, IsNullable = true, ColumnDescription = "事件类型")]
        public string? EventType { get; set; }

        /// <summary>
        /// äº‹ä»¶æè¿°
@@ -108,8 +108,8 @@
        /// <summary>
        /// æ˜¯å¦æˆåŠŸ
        /// </summary>
        [SugarColumn(IsNullable = false, ColumnDescription = "是否成功")]
        public bool IsSuccess { get; set; }
        [SugarColumn(IsNullable = true, ColumnDescription = "是否成功")]
        public bool? IsSuccess { get; set; }

        /// <summary>
        /// è€—时（毫秒）
@@ -133,38 +133,37 @@
        /// <summary>
        /// ä¸»é”®ID
        /// </summary>
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        [SugarColumn(ColumnName = "ID", OracleSequenceName = "SEQ_ERROR_LOG_ID", IsPrimaryKey = true)]
        public long Id { get; set; }

        /// <summary>
        /// é”™è¯¯å‘生时间
        /// </summary>
        [SugarColumn(IsNullable = false, ColumnDescription = "错误发生时间")]
        public DateTime ErrorTime { get; set; }
        [SugarColumn(IsNullable = true, ColumnDescription = "错误发生时间")]
        public DateTime? ErrorTime { get; set; }

        /// <summary>
        /// é¡¹ç›®åç§°
        /// </summary>
        [SugarColumn(Length = 100, IsNullable = false, ColumnDescription = "项目名称")]
        public string ProjectName { get; set; } = string.Empty;

        [SugarColumn(Length = 100, IsNullable = true, ColumnDescription = "项目名称")]
        public string? ProjectName { get; set; } 
        /// <summary>
        /// PLC IP地址
        /// </summary>
        [SugarColumn(Length = 50, IsNullable = false, ColumnDescription = "PLC IP地址")]
        public string PlcIpAddress { get; set; } = string.Empty;
        [SugarColumn(Length = 50, IsNullable = true, ColumnDescription = "PLC IP地址")]
        public string? PlcIpAddress { get; set; }

        /// <summary>
        /// é”™è¯¯ç±»åž‹
        /// </summary>
        [SugarColumn(Length = 100, IsNullable = false, ColumnDescription = "错误类型")]
        public string ErrorType { get; set; } = string.Empty;
        [SugarColumn(Length = 100, IsNullable = true, ColumnDescription = "错误类型")]
        public string? ErrorType { get; set; }

        /// <summary>
        /// é”™è¯¯æ¶ˆæ¯
        /// </summary>
        [SugarColumn(Length = 1000, IsNullable = false, ColumnDescription = "错误消息")]
        public string ErrorMessage { get; set; } = string.Empty;
        [SugarColumn(Length = 1000, IsNullable = true, ColumnDescription = "错误消息")]
        public string? ErrorMessage { get; set; }

        /// <summary>
        /// å¼‚常堆栈信息
@@ -175,20 +174,20 @@
        /// <summary>
        /// é”™è¯¯ä¸¥é‡çº§åˆ«ï¼ˆCritical, High, Medium, Low）
        /// </summary>
        [SugarColumn(Length = 20, IsNullable = false, ColumnDescription = "错误严重级别")]
        public string Severity { get; set; } = "Medium";
        [SugarColumn(Length = 20, IsNullable = true, ColumnDescription = "错误严重级别")]
        public string? Severity { get; set; } = "Medium";

        /// <summary>
        /// é‡è¯•æ¬¡æ•°
        /// </summary>
        [SugarColumn(IsNullable = false, ColumnDescription = "重试次数")]
        public int RetryCount { get; set; } = 0;
        [SugarColumn(IsNullable = true, ColumnDescription = "重试次数")]
        public int? RetryCount { get; set; } = 0;

        /// <summary>
        /// æ˜¯å¦å·²è§£å†³
        /// </summary>
        [SugarColumn(IsNullable = false, ColumnDescription = "是否已解决")]
        public bool IsResolved { get; set; } = false;
        [SugarColumn(IsNullable = true, ColumnDescription = "是否已解决")]
        public bool? IsResolved { get; set; } = false;
    }

    /// <summary>
@@ -200,98 +199,98 @@
        /// <summary>
        /// ä¸»é”®ID
        /// </summary>
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        [SugarColumn(ColumnName = "ID", OracleSequenceName = "SEQ_STATISTICS_ID", IsPrimaryKey = true)]
        public long Id { get; set; }

        /// <summary>
        /// ç»Ÿè®¡æ—¶é—´
        /// </summary>
        [SugarColumn(IsNullable = false, ColumnDescription = "统计时间")]
        public DateTime StatisticsTime { get; set; }
        [SugarColumn(IsNullable = true, ColumnDescription = "统计时间")]
        public DateTime? StatisticsTime { get; set; }

        /// <summary>
        /// é¡¹ç›®åç§°
        /// </summary>
        [SugarColumn(Length = 100, IsNullable = false, ColumnDescription = "项目名称")]
        public string ProjectName { get; set; } = string.Empty;
        [SugarColumn(Length = 100, IsNullable = true, ColumnDescription = "项目名称")]
        public string? ProjectName { get; set; } 

        /// <summary>
        /// PLC IP地址
        /// </summary>
        [SugarColumn(Length = 50, IsNullable = false, ColumnDescription = "PLC IP地址")]
        public string PlcIpAddress { get; set; } = string.Empty;
        [SugarColumn(Length = 50, IsNullable = true, ColumnDescription = "PLC IP地址")]
        public string? PlcIpAddress { get; set; }

        /// <summary>
        /// è¿žæŽ¥å°è¯•æ€»æ•°
        /// </summary>
        [SugarColumn(IsNullable = false, ColumnDescription = "连接尝试总数")]
        public int TotalConnectionAttempts { get; set; }
        [SugarColumn(IsNullable = true, ColumnDescription = "连接尝试总数")]
        public int? TotalConnectionAttempts { get; set; }

        /// <summary>
        /// è¿žæŽ¥æˆåŠŸæ€»æ•°
        /// </summary>
        [SugarColumn(IsNullable = false, ColumnDescription = "连接成功总数")]
        public int SuccessfulConnections { get; set; }
        [SugarColumn(IsNullable = true, ColumnDescription = "连接成功总数")]
        public int? SuccessfulConnections { get; set; }

        /// <summary>
        /// æ•°æ®è¯»å–总数
        /// </summary>
        [SugarColumn(IsNullable = false, ColumnDescription = "数据读取总数")]
        public int TotalDataReads { get; set; }
        [SugarColumn(IsNullable = true, ColumnDescription = "数据读取总数")]
        public int? TotalDataReads { get; set; }

        /// <summary>
        /// æ•°æ®è¯»å–成功总数
        /// </summary>
        [SugarColumn(IsNullable = false, ColumnDescription = "数据读取成功总数")]
        public int SuccessfulDataReads { get; set; }
        [SugarColumn(IsNullable = true, ColumnDescription = "数据读取成功总数")]
        public int? SuccessfulDataReads { get; set; }

        /// <summary>
        /// é”™è¯¯æ€»æ•°
        /// </summary>
        [SugarColumn(IsNullable = false, ColumnDescription = "错误总数")]
        public int TotalErrors { get; set; }
        [SugarColumn(IsNullable = true, ColumnDescription = "错误总数")]
        public int? TotalErrors { get; set; }

        /// <summary>
        /// å¹³å‡å“åº”时间（毫秒）
        /// </summary>
        [SugarColumn(IsNullable = false, ColumnDescription = "平均响应时间毫秒")]
        public double AverageResponseTimeMs { get; set; }
        [SugarColumn(IsNullable = true, ColumnDescription = "平均响应时间毫秒")]
        public double? AverageResponseTimeMs { get; set; }

        /// <summary>
        /// æœ€å¤§å“åº”时间（毫秒）
        /// </summary>
        [SugarColumn(IsNullable = false, ColumnDescription = "最大响应时间毫秒")]
        public int MaxResponseTimeMs { get; set; }
        [SugarColumn(IsNullable = true, ColumnDescription = "最大响应时间毫秒")]
        public int? MaxResponseTimeMs { get; set; }

        /// <summary>
        /// æœ€å°å“åº”时间（毫秒）
        /// </summary>
        [SugarColumn(IsNullable = false, ColumnDescription = "最小响应时间毫秒")]
        public int MinResponseTimeMs { get; set; }
        [SugarColumn(IsNullable = true, ColumnDescription = "最小响应时间毫秒")]
        public int? MinResponseTimeMs { get; set; }

        /// <summary>
        /// è¿žæŽ¥æˆåŠŸçŽ‡
        /// </summary>
        [SugarColumn(IsNullable = false, ColumnDescription = "连接成功率")]
        public double ConnectionSuccessRate { get; set; }
        [SugarColumn(IsNullable = true, ColumnDescription = "连接成功率")]
        public double? ConnectionSuccessRate { get; set; }

        /// <summary>
        /// æ•°æ®è¯»å–成功率
        /// </summary>
        [SugarColumn(IsNullable = false, ColumnDescription = "数据读取成功率")]
        public double DataReadSuccessRate { get; set; }
        [SugarColumn(IsNullable = true, ColumnDescription = "数据读取成功率")]
        public double? DataReadSuccessRate { get; set; }

        /// <summary>
        /// ç»Ÿè®¡æ—¶é—´èŒƒå›´å¼€å§‹
        /// </summary>
        [SugarColumn(IsNullable = false, ColumnDescription = "统计时间范围开始")]
        public DateTime PeriodStart { get; set; }
        [SugarColumn(IsNullable = true, ColumnDescription = "统计时间范围开始")]
        public DateTime? PeriodStart { get; set; }

        /// <summary>
        /// ç»Ÿè®¡æ—¶é—´èŒƒå›´ç»“束
        /// </summary>
        [SugarColumn(IsNullable = false, ColumnDescription = "统计时间范围结束")]
        public DateTime PeriodEnd { get; set; }
        [SugarColumn(IsNullable = true, ColumnDescription = "统计时间范围结束")]
        public DateTime? PeriodEnd { get; set; }
    }

    /// <summary>
@@ -299,14 +298,14 @@
    /// </summary>
    public static class EventTypes
    {
        public const string Connected = "Connected";
        public const string Disconnected = "Disconnected";
        public const string DataReceived = "DataReceived";
        public const string ConnectionError = "ConnectionError";
        public const string DataReadError = "DataReadError";
        public const string ConfigurationLoaded = "ConfigurationLoaded";
        public const string SystemStarted = "SystemStarted";
        public const string SystemStopped = "SystemStopped";
        public const string? Connected = "Connected";
        public const string? Disconnected = "Disconnected";
        public const string? DataReceived = "DataReceived";
        public const string? ConnectionError = "ConnectionError";
        public const string? DataReadError = "DataReadError";
        public const string? ConfigurationLoaded = "ConfigurationLoaded";
        public const string? SystemStarted = "SystemStarted";
        public const string? SystemStopped = "SystemStopped";
    }

    /// <summary>
@@ -314,9 +313,9 @@
    /// </summary>
    public static class ErrorSeverity
    {
        public const string Critical = "Critical";  // ç³»ç»Ÿæ— æ³•ç»§ç»­è¿è¡Œ
        public const string High = "High";          // åŠŸèƒ½å—到严重影响
        public const string Medium = "Medium";      // åŠŸèƒ½å—到影响但可继续
        public const string Low = "Low";            // è½»å¾®é—®é¢˜ï¼Œä¸å½±å“ä¸»è¦åŠŸèƒ½
        public const string? Critical = "Critical";  // ç³»ç»Ÿæ— æ³•ç»§ç»­è¿è¡Œ
        public const string? High = "High";          // åŠŸèƒ½å—到严重影响
        public const string? Medium = "Medium";      // åŠŸèƒ½å—到影响但可继续
        public const string? Low = "Low";            // è½»å¾®é—®é¢˜ï¼Œä¸å½±å“ä¸»è¦åŠŸèƒ½
    }
}

 Database/DatabaseManager.cs

@@ -251,12 +251,15 @@
        {
            try
            {
                OnDatabaseLog("开始使用原生Oracle SQL创建表...");
                OnDatabaseLog("开始使用原生Oracle SQL创建表和序列...");
                
                using (var connection = new OracleConnection(_config.ConnectionString))
                {
                    connection.Open();
                    OnDatabaseLog("Oracle连接已打开，开始执行建表脚本...");

                    // é¦–先创建所有序列
                    CreateOracleSequences(connection);

                    // 1. åˆ›å»ºModbusData表（主要数据表）
                    var createModbusDataTable = $@"
@@ -381,6 +384,58 @@
            catch (Exception ex)
            {
                OnDatabaseError($"Oracle建表失败: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// åˆ›å»ºOracle序列（兼容Oracle 11g）
        /// </summary>
        /// <param name="connection">Oracle数据库连接</param>
        private void CreateOracleSequences(OracleConnection connection)
        {
            try
            {
                OnDatabaseLog("开始创建Oracle序列...");
                
                // å®šä¹‰éœ€è¦åˆ›å»ºçš„序列
                var sequences = new[]
                {
                    "SEQ_MODBUS_DATA_ID",
                    "SEQ_COMMUNICATION_LOG_ID", 
                    "SEQ_ERROR_LOG_ID",
                    "SEQ_STATISTICS_ID"
                };

                foreach (var sequenceName in sequences)
                {
                    var createSequenceSQL = $@"
                        BEGIN
                            EXECUTE IMMEDIATE 'CREATE SEQUENCE {sequenceName}
                                START WITH 1
                                INCREMENT BY 1
                                NOMAXVALUE
                                NOCYCLE
                                CACHE 20';
                        EXCEPTION
                            WHEN OTHERS THEN
                                IF SQLCODE != -955 THEN -- -955 = name already used
                                    RAISE;
                                END IF;
                        END;";

                    using (var cmd = new OracleCommand(createSequenceSQL, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }
                    OnDatabaseLog($"序列 {sequenceName} æ£€æŸ¥å®Œæˆ");
                }
                
                OnDatabaseLog("所有Oracle序列创建完成");
            }
            catch (Exception ex)
            {
                OnDatabaseError($"Oracle序列创建失败: {ex.Message}");
                throw;
            }
        }
@@ -696,7 +751,7 @@

                // æ¸…理已解决的错误日志
                var deletedErrors = await _db.Deleteable<ErrorLogEntity>()
                    .Where(x => x.ProjectName == _projectName && x.ErrorTime < cutoffDate && x.IsResolved)
                    .Where(x => x.ProjectName == _projectName && x.ErrorTime < cutoffDate && x.IsResolved == true)
                    .ExecuteCommandAsync();

                totalDeleted = deletedData + deletedLogs + deletedErrors;