CCDCount/TestWork.DLL/SqlDataClass/ActionMesSqliteDataClass.cs

using CCDCount.MODEL.ShuLiModel; // 确保引用ActiveObjectClass所在命名空间
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Text;
using System.Threading;

namespace CCDCount.DLL.SqlDataClass
{
    public class ActionMesSqliteDataClass
    {
        private string DatabaseFile;
        private readonly string _connectionString;
        private object locker = new object();

        private readonly ConcurrentQueue<ActiveObjectClass> _dataQueue = new ConcurrentQueue<ActiveObjectClass>();
        private readonly Timer _timer;
        private const int MaxBatchSize = 1000; // 单次最大插入条数（受 SQLite 参数上限限制）
        private const int FlushIntervalMs = 5000; // 最大等待时间（毫秒）

        public ActionMesSqliteDataClass(string dbPath)
        {
            DatabaseFile = dbPath;
            _connectionString = $"Data Source={DatabaseFile};Version=3;"+
                "Journal Mode=WAL;" +           // 使用WAL模式提高并发性
                "Synchronous=Normal;" +         // 平衡性能和安全性
                "Cache Size=10000;" +           // 增加缓存大小
                "Pooling=true;" +               // 启用连接池
                "Max Pool Size=100;";           // 设置最大连接池大小
            string directoryPath = Path.GetDirectoryName(DatabaseFile);
            if (!Directory.Exists(directoryPath))
            {
                // 创建文件夹
                Directory.CreateDirectory(directoryPath);
            }
            InitializeDatabase();
            _timer = new Timer(FlushData, null, FlushIntervalMs, FlushIntervalMs);
        }

        private void InitializeDatabase()
        {
            if (!File.Exists(DatabaseFile))
            {
                SQLiteConnection.CreateFile(DatabaseFile);
            }

            using (var conn = new SQLiteConnection(_connectionString))
            {
                conn.Open();

                // 创建主表
                const string createActiveObjectTable = @"
                CREATE TABLE IF NOT EXISTS ActiveObject (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Num INTEGER NOT NULL,
                    MinStartCol INTEGER NOT NULL,
                    MaxEndCol INTEGER NOT NULL,
                    LastSeenLineStartCol INTEGER NOT NULL,
                    LastSeenLineEndCol INTEGER NOT NULL,
                    StartLine INTEGER NOT NULL,
                    LastSeenLine INTEGER NOT NULL,
                    StartCheckTime TEXT NOT NULL,
                    EndCheckTime TEXT NOT NULL,
                    Area INTEGER NOT NULL,
                    MaxLength REAL NOT NULL,
                    ChannelNO INTEGER NOT NULL,
                    ImageWidth INTEGER NOT NULL,
                    StateCode INTEGER NOT NULL DEFAULT -1,
                    BatchNumber TEXT NOT NULL,
                    hasSignificantConcavity INTEGER CHECK (hasSignificantConcavity IN (0, 1)),
                    concavityRatio REAL NOT NULL
                )";

                // 创建从表
                const string createRowDataTable = @"
                CREATE TABLE IF NOT EXISTS RowData (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    ActiveObjectId INTEGER NOT NULL,
                    RowsCol INTEGER NOT NULL,
                    StartCol INTEGER NOT NULL,
                    EndCol INTEGER NOT NULL,
                    FOREIGN KEY (ActiveObjectId) 
                        REFERENCES ActiveObject(Id) ON DELETE CASCADE
                )";

                using (var cmd = new SQLiteCommand(conn))
                {
                    cmd.CommandText = createActiveObjectTable;
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = createRowDataTable;
                    cmd.ExecuteNonQuery();
                }
            }
        }

        // 插入ActiveObjectClass对象及其关联数据
        public void AddData(ActiveObjectClass data)
        {
            _dataQueue.Enqueue(data);

            // 如果缓存数量达到上限，则立即触发写入
            if (_dataQueue.Count >= MaxBatchSize)
            {
                FlushData(null);
            }
        }

        // 强制刷新缓存数据到数据库
        private void FlushData(object state)
        {
            lock (locker)
            {
                if (_dataQueue.IsEmpty) return;

                var batch = new List<ActiveObjectClass>();
                while (_dataQueue.TryDequeue(out var item))
                {
                    batch.Add(item);
                }

                WriteBatchToDatabase(batch);
            }
        }
        private void WriteBatchToDatabase(List<ActiveObjectClass> batch)
        {
            using (var conn = new SQLiteConnection(_connectionString))
            {
                conn.Open();
                using (var transaction = conn.BeginTransaction())
                {
                    try
                    {
                        // 构建批量插入语句
                        var sqlBuilder = new StringBuilder();
                        sqlBuilder.Append(@"
                        INSERT INTO ActiveObject (
                            Num, MinStartCol, MaxEndCol, LastSeenLineStartCol, 
                            LastSeenLineEndCol, StartLine, LastSeenLine, 
                            StartCheckTime, EndCheckTime, Area, MaxLength,
                            ChannelNO, ImageWidth, StateCode, BatchNumber,
                            hasSignificantConcavity, concavityRatio
                        ) VALUES ");

                        var parameters = new List<object>();
                        for (int i = 0; i < batch.Count; i++)
                        {
                            var obj = batch[i];
                            sqlBuilder.Append($"(@p{i * 17}, @p{i * 17 + 1}, @p{i * 17 + 2}, @p{i * 17 + 3}, " +
                                              $"@p{i * 17 + 4}, @p{i * 17 + 5}, @p{i * 17 + 6}, " +
                                              $"@p{i * 17 + 7}, @p{i * 17 + 8}, @p{i * 17 + 9}, " +
                                              $"@p{i * 17 + 10}, @p{i * 17 + 11}, @p{i * 17 + 12}, " +
                                              $"@p{i * 17 + 13}, @p{i * 17 + 14}, @p{i * 17 + 15}, @p{i * 17 + 16})");

                            if (i < batch.Count - 1)
                                sqlBuilder.Append(", ");

                            // 绑定参数值
                            parameters.AddRange(new object[]
                            {
                            obj.Num, obj.MinStartCol, obj.MaxEndCol, obj.LastSeenLineStartCol,
                            obj.LastSeenLineEndCol, obj.StartLine, obj.LastSeenLine,
                            obj.StartCheckTime.ToString("o"), obj.EndCheckTime.ToString("o"),
                            obj.Area, obj.MaxLength, obj.ChannelNO, obj.ImageWidth,
                            obj.StateCode, obj.BatchNumber, obj.hasSignificantConcavity, obj.concavityRatio
                            });
                        }

                        // 执行批量插入
                        using (var cmd = new SQLiteCommand(sqlBuilder.ToString(), conn))
                        {
                            for (int i = 0; i < parameters.Count; i++)
                            {
                                cmd.Parameters.AddWithValue($"@p{i}", parameters[i]);
                            }

                            cmd.ExecuteNonQuery();
                        }

                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public void InsertActiveObject(ActiveObjectClass activeObject)
        {
            lock (locker)
            {
                using (var conn = new SQLiteConnection(_connectionString))
                {
                    conn.Open();
                    using (var transaction = conn.BeginTransaction())
                    {
                        try
                        {
                            // 插入主表数据
                            const string insertActiveObject = @"
                            INSERT INTO ActiveObject (
                            Num, MinStartCol, MaxEndCol, LastSeenLineStartCol, 
                            LastSeenLineEndCol, StartLine, LastSeenLine, 
                            StartCheckTime, EndCheckTime, Area, MaxLength,
                            ChannelNO, ImageWidth, StateCode, BatchNumber,
                            hasSignificantConcavity,concavityRatio
                            ) VALUES (
                            @Num, @MinStartCol, @MaxEndCol, @LastSeenLineStartCol, 
                            @LastSeenLineEndCol, @StartLine, @LastSeenLine, 
                            @StartCheckTime, @EndCheckTime, @Area, @MaxLength,
                            @ChannelNO, @ImageWidth, @StateCode, @BatchNumber,
                            @hasSignificantConcavity,@concavityRatio
                        ); SELECT last_insert_rowid();";

                            using (var cmd = new SQLiteCommand(insertActiveObject, conn))
                            {
                                cmd.Parameters.AddWithValue("@Num", activeObject.Num);
                                cmd.Parameters.AddWithValue("@MinStartCol", activeObject.MinStartCol);
                                cmd.Parameters.AddWithValue("@MaxEndCol", activeObject.MaxEndCol);
                                cmd.Parameters.AddWithValue("@LastSeenLineStartCol", activeObject.LastSeenLineStartCol);
                                cmd.Parameters.AddWithValue("@LastSeenLineEndCol", activeObject.LastSeenLineEndCol);
                                cmd.Parameters.AddWithValue("@StartLine", activeObject.StartLine);
                                cmd.Parameters.AddWithValue("@LastSeenLine", activeObject.LastSeenLine);
                                cmd.Parameters.AddWithValue("@StartCheckTime", activeObject.StartCheckTime.ToString("o"));
                                cmd.Parameters.AddWithValue("@EndCheckTime", activeObject.EndCheckTime.ToString("o"));
                                cmd.Parameters.AddWithValue("@Area", activeObject.Area);
                                cmd.Parameters.AddWithValue("@MaxLength", activeObject.MaxLength);
                                cmd.Parameters.AddWithValue("@ChannelNO", activeObject.ChannelNO);
                                cmd.Parameters.AddWithValue("@ImageWidth", activeObject.ImageWidth);
                                cmd.Parameters.AddWithValue("@StateCode", activeObject.StateCode);
                                cmd.Parameters.AddWithValue("@BatchNumber", activeObject.BatchNumber);
                                cmd.Parameters.AddWithValue("@hasSignificantConcavity", activeObject.hasSignificantConcavity);
                                cmd.Parameters.AddWithValue("@concavityRatio", activeObject.concavityRatio);

                                // 获取新插入的主键ID
                                var activeObjectId = Convert.ToInt32(cmd.ExecuteScalar());

                                // 插入关联的RowData
                                InsertRowData(conn, activeObjectId, activeObject.RowsData);
                            }

                            transaction.Commit();
                        }
                        catch
                        {
                            transaction.Rollback();
                            throw;
                        }
                    }
                }
            }
        }

        // 插入RowData数据
        private void InsertRowData(SQLiteConnection conn, int activeObjectId, List<RowStartEndCol> rowsData)
        {
            const string insertRowData = @"
            INSERT INTO RowData (
                ActiveObjectId, RowsCol, StartCol, EndCol
            ) VALUES (
                @ActiveObjectId, @RowsCol, @StartCol, @EndCol
            )";

            using (var cmd = new SQLiteCommand(insertRowData, conn))
            {
                foreach (var row in rowsData)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@ActiveObjectId", activeObjectId);
                    cmd.Parameters.AddWithValue("@RowsCol", row.RowsCol);
                    cmd.Parameters.AddWithValue("@StartCol", row.StartCol);
                    cmd.Parameters.AddWithValue("@EndCol", row.EndCol);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        // 根据ID查询完整对象
        public ActiveObjectClass GetActiveObjectById(int id)
        {
            using (var conn = new SQLiteConnection(_connectionString))
            {
                conn.Open();
                const string query = @"
                SELECT 
                    ao.*,
                    rd.Id AS RowId, rd.RowsCol, rd.StartCol, rd.EndCol
                FROM ActiveObject ao
                LEFT JOIN RowData rd ON ao.Id = rd.ActiveObjectId
                WHERE ao.Id = @Id";

                using (var cmd = new SQLiteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@Id", id);

                    using (var reader = cmd.ExecuteReader())
                    {
                        ActiveObjectClass activeObject = null;
                        var rowsData = new List<RowStartEndCol>();

                        while (reader.Read())
                        {
                            // 只初始化主对象一次
                            if (activeObject == null)
                            {
                                activeObject = new ActiveObjectClass
                                {
                                    Num = Convert.ToInt32(reader["Num"]),
                                    MinStartCol = Convert.ToInt32(reader["MinStartCol"]),
                                    MaxEndCol = Convert.ToInt32(reader["MaxEndCol"]),
                                    LastSeenLineStartCol = Convert.ToInt32(reader["LastSeenLineStartCol"]),
                                    LastSeenLineEndCol = Convert.ToInt32(reader["LastSeenLineEndCol"]),
                                    StartLine = Convert.ToInt64(reader["StartLine"]),
                                    LastSeenLine = Convert.ToInt64(reader["LastSeenLine"]),
                                    StartCheckTime = DateTime.Parse(reader["StartCheckTime"].ToString()),
                                    EndCheckTime = DateTime.Parse(reader["EndCheckTime"].ToString()),
                                    Area = Convert.ToInt32(reader["Area"]),
                                    MaxLength = Convert.ToDouble(reader["MaxLength"]),
                                    ChannelNO = Convert.ToInt32(reader["ChannelNO"]),
                                    ImageWidth = Convert.ToInt32(reader["ImageWidth"]),
                                    StateCode = Convert.ToInt32(reader["StateCode"]),
                                    BatchNumber = reader["BatchNumber"].ToString(),
                                    RowsData = rowsData
                                };
                            }

                            // 添加行数据（确保RowData记录存在）
                            if (!reader.IsDBNull(reader.GetOrdinal("RowId")))
                            {
                                rowsData.Add(new RowStartEndCol
                                {
                                    RowsCol = Convert.ToInt64(reader["RowsCol"]),
                                    StartCol = Convert.ToInt32(reader["StartCol"]),
                                    EndCol = Convert.ToInt32(reader["EndCol"])
                                });
                            }
                        }
                        return activeObject;
                    }
                }
            }
        }

        public List<string> GetAllBatchNumber()
        {
            List<string> BatchNumbers = new List<string>();
            using (var conn = new SQLiteConnection(_connectionString))
            {
                conn.Open();
                const string query = @"SELECT DISTINCT BatchNumber FROM ActiveObject";

                using (var cmd = new SQLiteCommand(query, conn))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            BatchNumbers.Add(reader["BatchNumber"].ToString());
                        }
                    }
                }
            }
            return BatchNumbers;
        }

        public List<ActiveObjectClass> GetActiveObjectByBatchNumber(string BatchNumber)
        {
            List<ActiveObjectClass> activeObjects = new List<ActiveObjectClass>();
            using (var conn = new SQLiteConnection(_connectionString))
            {
                conn.Open();
                const string query = @"SELECT * FROM ActiveObject WHERE BatchNumber == @BatchNumber ORDER BY Id";

                using (var cmd = new SQLiteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@BatchNumber", BatchNumber);

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            object value = reader["Id"];
                            if (!Convert.IsDBNull(value))
                            {
                                activeObjects.Add(new ActiveObjectClass()
                                {
                                    Num = Convert.ToInt32(reader["Num"]),
                                    MinStartCol = Convert.ToInt32(reader["MinStartCol"]),
                                    MaxEndCol = Convert.ToInt32(reader["MaxEndCol"]),
                                    LastSeenLineStartCol = Convert.ToInt32(reader["LastSeenLineStartCol"]),
                                    LastSeenLineEndCol = Convert.ToInt32(reader["LastSeenLineEndCol"]),
                                    StartLine = Convert.ToInt64(reader["StartLine"]),
                                    LastSeenLine = Convert.ToInt64(reader["LastSeenLine"]),
                                    StartCheckTime = DateTime.Parse(reader["StartCheckTime"].ToString()),
                                    EndCheckTime = DateTime.Parse(reader["EndCheckTime"].ToString()),
                                    Area = Convert.ToInt32(reader["Area"]),
                                    MaxLength = Convert.ToDouble(reader["MaxLength"]),
                                    ChannelNO = Convert.ToInt32(reader["ChannelNO"]),
                                    ImageWidth = Convert.ToInt32(reader["ImageWidth"]),
                                    StateCode = Convert.ToInt32(reader["StateCode"]),
                                    BatchNumber = reader["BatchNumber"].ToString(),
                                });
                            }
                        }
                    }
                }
            }
            return activeObjects;
        }

        // 按页码获取数据
        public List<ActiveObjectClass> GetActiveObjectForPage(int StartLine, int EndLine)
        {
            List<ActiveObjectClass> activeObjects = new List<ActiveObjectClass>();
            try
            {
                using (var conn = new SQLiteConnection(_connectionString))
                {
                    conn.Open();
                    const string query = @"
                SELECT 
                    ao.*,
                    rd.Id AS RowId, rd.RowsCol, rd.StartCol, rd.EndCol
                FROM ActiveObject ao
                LEFT JOIN RowData rd ON ao.Id = rd.ActiveObjectId
                WHERE ao.StartLine <= @StartLine AND LastSeenLine>=@LastSeenLine ORDER BY rd.Id";

                    using (var cmd = new SQLiteCommand(query, conn))
                    {
                        cmd.Parameters.AddWithValue("@StartLine", EndLine);
                        cmd.Parameters.AddWithValue("@LastSeenLine", StartLine);

                        using (var reader = cmd.ExecuteReader())
                        {
                            int NowId = -1;
                            ActiveObjectClass activeObject = null;
                            var rowsData = new List<RowStartEndCol>();

                            while (reader.Read())
                            {
                                object value = reader["Id"];
                                if (!Convert.IsDBNull(value))
                                {
                                    if (NowId != Convert.ToInt32(reader["Id"]))
                                    {
                                        if (activeObject != null)
                                        {
                                            activeObjects.Add(activeObject);
                                            activeObject = null;
                                            rowsData = new List<RowStartEndCol>();
                                        }
                                        NowId = Convert.ToInt32(reader["Id"]);
                                    }
                                    // 只初始化主对象一次
                                    if (activeObject == null)
                                    {
                                        activeObject = new ActiveObjectClass
                                        {
                                            Num = Convert.ToInt32(reader["Num"]),
                                            MinStartCol = Convert.ToInt32(reader["MinStartCol"]),
                                            MaxEndCol = Convert.ToInt32(reader["MaxEndCol"]),
                                            LastSeenLineStartCol = Convert.ToInt32(reader["LastSeenLineStartCol"]),
                                            LastSeenLineEndCol = Convert.ToInt32(reader["LastSeenLineEndCol"]),
                                            StartLine = Convert.ToInt64(reader["StartLine"]),
                                            LastSeenLine = Convert.ToInt64(reader["LastSeenLine"]),
                                            StartCheckTime = DateTime.Parse(reader["StartCheckTime"].ToString()),
                                            EndCheckTime = DateTime.Parse(reader["EndCheckTime"].ToString()),
                                            Area = Convert.ToInt32(reader["Area"]),
                                            MaxLength = Convert.ToDouble(reader["MaxLength"]),
                                            ChannelNO = Convert.ToInt32(reader["ChannelNO"]),
                                            ImageWidth = Convert.ToInt32(reader["ImageWidth"]),
                                            StateCode = Convert.ToInt32(reader["StateCode"]),
                                            BatchNumber = reader["BatchNumber"].ToString(),
                                            RowsData = rowsData
                                        };
                                    }

                                    // 添加行数据（确保RowData记录存在）
                                    rowsData.Add(new RowStartEndCol
                                    {
                                        RowsCol = Convert.ToInt64(reader["RowsCol"]),
                                        StartCol = Convert.ToInt32(reader["StartCol"]),
                                        EndCol = Convert.ToInt32(reader["EndCol"])
                                    });
                                }
                            }

                            if (activeObject != null)
                            {
                                activeObjects.Add(activeObject);
                                activeObject = null;
                                rowsData = new List<RowStartEndCol>();
                            }
                        }
                    }
                }
            }
            catch(Exception ex)
            {
                Console.WriteLine($"GetActiveObjectForPage - Error:{ex.Message}");
            }
            return activeObjects;
        }

        public void GetAllActionMinStartMaxEndLine(out int Num, out int StartLine, out int EndLine)
        {
            using (var conn = new SQLiteConnection(_connectionString))
            {
                conn.Open();
                const string query = @"
                SELECT MAX(Num) AS Num, MAX(LastSeenLine) AS MaxLastSeenLine, MIN(StartLine) AS MinStartLine FROM ActiveObject";

                int MinStartLine = 0;
                int MaxLastSeenLine = 0;
                int StartNum = 0;

                using (var cmd = new SQLiteCommand(query, conn))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            object value = reader["MinStartLine"];
                            if (!Convert.IsDBNull(value))
                            {
                                MinStartLine = Convert.ToInt32(reader["MinStartLine"]);
                                MaxLastSeenLine = Convert.ToInt32(reader["MaxLastSeenLine"]);
                                StartNum = Convert.ToInt32(reader["Num"]);
                            }
                        }
                    }
                }
                StartLine = MinStartLine;
                EndLine = MaxLastSeenLine;
                Num = StartNum;
            }
            
        }
        // 根据Num查询ID
        public int GetActiveIdByNum(int Num)
        {
            int result = -1;
            using (var conn = new SQLiteConnection(_connectionString))
            {
                conn.Open();
                const string query = @"
                SELECT * FROM ActiveObject WHERE Num = @Num";

                using (var cmd = new SQLiteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@Num", Num);

                    using (var reader = cmd.ExecuteReader())
                    {
                        var rowsData = new List<RowStartEndCol>();

                        while (reader.Read())
                        {
                            object value = reader["Id"];
                            if (!Convert.IsDBNull(value))
                            {
                                result = Convert.ToInt32(reader["Id"]);
                            }
                        }
                    }
                }
            }
            return result;
        }

        // 关闭定时器并释放资源
        public void Dispose()
        {
            _timer?.Dispose();
            FlushData(null); // 确保最后一批数据被写入
        }
    }
}