BufferSQLite.cs

using FLY.OBJComponents.Common;
using FLY.OBJComponents.IService;
using FObjBase;
using SQLite;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FLY.OBJComponents.Server
{
    public class BufferSQLite<TLc, TDb> : IBufferAdd<TLc>
        where TLc : IDbBase, new()
        where TDb : IDbBase, new()

    {
        DBTable<TDb> dbTable;
        SQLiteHelper sqliteHelper;
        Func<TLc, TDb> mapLc2Db;
        Func<TDb, TLc> mapDb2Lc;

        public int NewestID => Count - 1;

        public int Count { get; private set; }

        /// <summary>
        /// 不能设置，设了也没有用
        /// </summary>
        public int Capacity { get; set; } = int.MaxValue;

        public event NotifyBufferChangedEventHandler<TLc> BufferChanged;
        public event PropertyChangedEventHandler PropertyChanged;


        public BufferSQLite()
        {

        }
        public void Init(DBTable<TDb> dbTable,
            Func<TLc, TDb> mapLc2Db, Func<TDb, TLc> mapDb2Lc)
        {
            
            this.dbTable = dbTable;
            this.sqliteHelper = dbTable.sqliteHelper;
            this.mapLc2Db = mapLc2Db;
            this.mapDb2Lc = mapDb2Lc;
            Load();
        }
        void Load()
        {
            var n = sqliteHelper.ExecuteScalar($"SELECT COUNT(*) FROM {dbTable.TableName}");
            int count = System.Convert.ToInt32(n);
            Count = count;
        }

        public void Add(TLc lc)
        {
            //添加 数据库必要的 field
            lc.ID = dbTable.FreeID;

            //SQLs
            List<string> sqls = new List<string>();
            sqls.Add(SQLiteHelper.GetInsertCommandText(mapLc2Db(lc)));
            sqliteHelper.QueryTranAsync(sqls);

            Count++;
            BufferChanged?.Invoke(this, new NotifyBufferChangedEventArgs<TLc>()
            {
                Action = NotifyBufferChangedAction.Add,
                EndingID = NewestID,
                Count = 1,
                Items = new TLc[] { lc },
                BufferCount = Count,
                BufferNewestID = NewestID
            });
        }
        public void AddRange(IEnumerable<TLc> lcs)
        {
            if (lcs.Count() == 0)
                return;
            List<string> sqls = new List<string>();
            foreach (var lc in lcs)
            {
                //添加 数据库必要的 field
                lc.ID = dbTable.FreeID;
                //SQLs
                sqls.Add(SQLiteHelper.GetInsertCommandText(mapLc2Db(lc)));
            }
            sqliteHelper.QueryTranAsync(sqls);

            Count += lcs.Count();
            BufferChanged?.Invoke(this, new NotifyBufferChangedEventArgs<TLc>()
            {
                Action = NotifyBufferChangedAction.Add,
                EndingID = NewestID,
                Count = lcs.Count(),
                Items = lcs,
                BufferCount = Count,
                BufferNewestID = NewestID
            });
        }
        /// <summary>
        /// 清空全部数据
        /// </summary>
        public void Reset()
        {
            //SQLs
            List<string> sqls = new List<string>();
            sqls.Add($"DELETE FROM {dbTable.TableName}");
            sqliteHelper.QueryTranAsync(sqls);
            Count = 0;
            BufferChanged?.Invoke(this, new NotifyBufferChangedEventArgs<TLc>()
            {
                Action = NotifyBufferChangedAction.Reset,
                EndingID = -1,
                Count = 0,
                BufferCount = Count,
                BufferNewestID = NewestID
            });
        }

        /// <summary>
        /// 获取指定位置的N个数据
        /// </summary>
        /// <param name="last_id">最后的ID</param>
        /// <param name="count">数量</param>
        /// <param name="asyncCB">回调</param>
        /// <param name="asyncContext">回调里面的上下文</param>
        public async void GetRecord(int last_id, int count, AsyncCBHandler asyncCB, object asyncContext)
        {
            if (Count == 0)
            {
                asyncCB(asyncContext, new GetRecordReponse<TLc>()
                {
                    LastID = 0,
                    Items = null
                });
                return;
            }

            await Task.Factory.StartNew(() => {
                var reponse = new GetRecordReponse<TLc>();

                var dbs = dbTable.Find($"ORDER BY ID LIMIT {last_id - count + 1},{count}");
                reponse.LastID = last_id;
                List<TLc> lcs = new List<TLc>();
                foreach (var db in dbs)
                {
                    lcs.Add(mapDb2Lc(db));
                }
                reponse.Items = lcs;

                asyncCB(asyncContext, reponse);
            });

        }

        /// <summary>
        /// 获取最新的N个数据
        /// </summary>
        /// <param name="count">数量</param>
        /// <param name="asyncCB">回调</param>
        /// <param name="asyncContext">回调里面的上下文</param>
        public void GetRecord(int count, AsyncCBHandler asyncCB, object asyncContext)
        {
            GetRecord(NewestID, count, asyncCB, asyncContext);
        }
    }

    public class BufferSQLite<TDb> : IBufferAdd<TDb>
    where TDb : IDbBase, new()

    {
        DBTable<TDb> dbTable;
        SQLiteHelper sqliteHelper;
        public int NewestID => Count - 1;

        public int Count { get; private set; }

        /// <summary>
        /// 不能设置，设了也没有用
        /// </summary>
        public int Capacity { get; set; } = int.MaxValue;

        public event NotifyBufferChangedEventHandler<TDb> BufferChanged;
        public event PropertyChangedEventHandler PropertyChanged;


        public BufferSQLite()
        {

        }
        public void Init(DBTable<TDb> dbTable)
        {
            this.dbTable = dbTable;
            this.sqliteHelper = dbTable.sqliteHelper;
            Load();
        }
        void Load()
        {
            var n = sqliteHelper.ExecuteScalar($"SELECT COUNT(*) FROM {dbTable.TableName}");
            int count = System.Convert.ToInt32(n);
            Count = count;
        }

        public void Add(TDb db)
        {
            //添加 数据库必要的 field
            db.ID = dbTable.FreeID;

            //SQLs
            List<string> sqls = new List<string>();
            sqls.Add(SQLiteHelper.GetInsertCommandText(db));
            sqliteHelper.QueryTranAsync(sqls);

            Count++;
            BufferChanged?.Invoke(this, new NotifyBufferChangedEventArgs<TDb>()
            {
                Action = NotifyBufferChangedAction.Add,
                EndingID = NewestID,
                Count = 1,
                Items = new TDb[] { db},
                BufferCount = Count,
                BufferNewestID = NewestID
            });
        }
        public void AddRange(IEnumerable<TDb> dbs)
        {
            if (dbs.Count() == 0)
                return;
            List<string> sqls = new List<string>();
            foreach (var db in dbs)
            {
                //添加 数据库必要的 field
                db.ID = dbTable.FreeID;

                //SQLs
                sqls.Add(SQLiteHelper.GetInsertCommandText(db));
            }
            sqliteHelper.QueryTranAsync(sqls);

            Count += dbs.Count();
            BufferChanged?.Invoke(this, new NotifyBufferChangedEventArgs<TDb>()
            {
                Action = NotifyBufferChangedAction.Add,
                EndingID = NewestID,
                Count = dbs.Count(),
                Items = dbs,
                BufferCount = Count,
                BufferNewestID = NewestID
            });
        }
        /// <summary>
        /// 清空全部数据
        /// </summary>
        public void Reset()
        {
            //SQLs
            List<string> sqls = new List<string>();
            sqls.Add($"DELETE FROM {dbTable.TableName}");
            sqliteHelper.QueryTranAsync(sqls);
            Count = 0;
            BufferChanged?.Invoke(this, new NotifyBufferChangedEventArgs<TDb>()
            {
                Action = NotifyBufferChangedAction.Reset,
                EndingID = -1,
                Count = 0,
                BufferCount = Count,
                BufferNewestID = NewestID
            });
        }

        /// <summary>
        /// 获取指定位置的N个数据
        /// </summary>
        /// <param name="last_id">最后的ID</param>
        /// <param name="count">数量</param>
        /// <param name="asyncCB">回调</param>
        /// <param name="asyncContext">回调里面的上下文</param>
        public async void GetRecord(int last_id, int count, AsyncCBHandler asyncCB, object asyncContext)
        {
            if (Count == 0)
            {
                asyncCB(asyncContext, new GetRecordReponse<TDb>()
                {
                    LastID = 0,
                    Items = null
                });
                return;
            }

            await Task.Factory.StartNew(() => {
                var reponse = new GetRecordReponse<TDb>();

                var dbs = dbTable.Find($"ORDER BY ID LIMIT {last_id - count + 1},{count}");
                reponse.LastID = last_id;
                reponse.Items = dbs;
                asyncCB(asyncContext, reponse);
            });

        }

        /// <summary>
        /// 获取最新的N个数据
        /// </summary>
        /// <param name="count">数量</param>
        /// <param name="asyncCB">回调</param>
        /// <param name="asyncContext">回调里面的上下文</param>
        public void GetRecord(int count, AsyncCBHandler asyncCB, object asyncContext)
        {
            GetRecord(NewestID, count, asyncCB, asyncContext);
        }
    }
}