SQLiteDbContext.cs

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace SQLite
{
    public abstract class SQLiteDbContext
    {
        

        public List<IDBTable> DbSet = new List<IDBTable>();
        public SQLiteHelper sqliteHelper { get; private set; }
        string ConnectionString
        {
            get
            {
                return string.Format("Data Source={0};Version=3;", DBPath);
            }
        }
        public string DBPath { get; private set; } = @"test.sqlite3";

        public SQLiteDbContext(string dbPath)
        {
            DBPath = dbPath;
            Constructor();
        }
        void Constructor()
        {
            sqliteHelper = new SQLiteHelper();
            sqliteHelper.ConnectionString = ConnectionString;

            var type = GetType();
            foreach (var p in type.GetProperties())
            {
                if (typeof(IDBTable).IsAssignableFrom(p.PropertyType))
                {
                    DbSet.Add(p.GetValue(this) as IDBTable);
                }
            }
            foreach (IDBTable dBTable in DbSet)
            {
                dBTable.Init(sqliteHelper);
            }
        }
        public void SetDBPath(string dbPath)
        {
            DBPath = dbPath;
            sqliteHelper.ConnectionString = ConnectionString;
        }

        void Build()
        {

            string directoryname = System.IO.Path.GetDirectoryName(DBPath);

            if (!string.IsNullOrEmpty(directoryname) && !System.IO.Directory.Exists(directoryname))
                System.IO.Directory.CreateDirectory(directoryname);

            SQLiteConnection.CreateFile(DBPath);

            foreach (IDBTable dBTable in DbSet)
                dBTable.Create();

        }
        void Rebuild()
        {
            if (!System.IO.File.Exists(DBPath))
            {
                Build();
            }
            else
            {
                //把文件删除，重建
                System.IO.File.Delete(DBPath);
                Build();
            }
        }

        void Load()
        {
            foreach(var table in DbSet)
            {
                table.MaxID = LoadID(table.TableName);
            }
        }
        long LoadID(string tablename)
        {
            string cmd = $"SELECT MAX(ID) FROM {tablename}";
            var reponse = sqliteHelper.ExecuteScalar(cmd);
            if (reponse is DBNull)
                return -1;
            else
                return System.Convert.ToInt64(reponse);

        }

        /// <summary>
        /// 给定分区数 检测表是否存在，合法
        /// </summary>
        /// <param name="nbolts"></param>
        /// <returns>false 表不合法，重建; true 正常！</returns>
        public bool Init()
        {
            if (!System.IO.File.Exists(DBPath))
            {
                Build();
                return false;
            }
            //TODO, 表不对删除就好。。。没必要重新创建数据库

            //任意一个表不对，或者不存在，都必须重建
            Dictionary<string, string> ddls = new Dictionary<string, string>();
            foreach (IDBTable tb in DbSet)
            {
                ddls.Add(tb.TableName, tb.DDL);
            }
            bool isVaild = sqliteHelper.IsTableValid(ddls, out Dictionary<string, SQLiteHelper.IsTableValidResult> results);

            if (!isVaild)//不合法
            {
                if (results.Any(kv => kv.Value == SQLiteHelper.IsTableValidResult.FormatErr))
                {
                    //先备份

                    //File.Copy(DBPath, DBPath + $".{DateTime.Now:yyyyMMddHHmmss}.bk");

                    //重建数据库
                    File.Move(DBPath, DBPath + $".{DateTime.Now:yyyyMMddHHmmss}.bk");
                    Build();
                    return false;
                }
                //有表 不对
                foreach (var kv in results)
                {
                    switch (kv.Value)
                    {
                        case SQLiteHelper.IsTableValidResult.NotHere:
                            {
                                //直接创建表
                                sqliteHelper.ExecuteNonQuery(ddls[kv.Key]);
                            }
                            break;
                        //case SQLiteHelper.IsTableValidResult.FormatErr:
                        //    {
                        //        //先删除表，再创建
                        //        sqliteHelper.ExecuteNonQuery($"DROP TABLE {kv.Key}");
                        //        sqliteHelper.ExecuteNonQuery(ddls[kv.Key]);
                        //    }
                        //    break;
                    }
                }
            }


            //检查INDEX
            Dictionary<string, string> indexs = new Dictionary<string, string>();
            foreach (IDBTable tb in DbSet)
            {
                if(!string.IsNullOrEmpty(tb.INDEX))
                    indexs.Add(tb.TableName, tb.INDEX);
            }
            if (indexs.Count() > 0)
            {
                isVaild = sqliteHelper.IsIndexValid(indexs, out results);

                if (!isVaild)//不合法
                {
                    foreach (var kv in results)
                    {
                        switch (kv.Value)
                        {
                            case SQLiteHelper.IsTableValidResult.NotHere:
                                {
                                    //直接创建表
                                    sqliteHelper.ExecuteNonQuery(indexs[kv.Key]);
                                }
                                break;
                            case SQLiteHelper.IsTableValidResult.FormatErr:
                                {
                                    //先删除INDEX，再创建
                                    sqliteHelper.ExecuteNonQuery($"DROP INDEX {kv.Key}_INDEX");
                                    sqliteHelper.ExecuteNonQuery(indexs[kv.Key]);
                                }
                                break;
                        }
                    }
                }
            }
            
            //最后也要加载数据
            Load();
            return isVaild;
        }
        /// <summary>
        /// 当出错，不重建表
        /// </summary>
        /// <returns></returns>
        public bool InitNoBuild()
        {
            if (!System.IO.File.Exists(DBPath))
            {
                return false;
            }
            //TODO, 表不对删除就好。。。没必要重新创建数据库

            //任意一个表不对，或者不存在，都必须重建
            Dictionary<string, string> ddls = new Dictionary<string, string>();
            foreach (IDBTable tb in DbSet)
            {
                ddls.Add(tb.TableName, tb.DDL);
            }

            bool isVaild = sqliteHelper.IsTableValid(ddls, out Dictionary<string, SQLiteHelper.IsTableValidResult> results);

            if (!isVaild)//不合法
            {
                if (results.Any(kv => kv.Value == SQLiteHelper.IsTableValidResult.FormatErr))
                {
                    //格式不对，无法容忍
                    return false;
                }

                //有表 不对
                foreach (var kv in results)
                {
                    switch (kv.Value)
                    {
                        case SQLiteHelper.IsTableValidResult.NotHere:
                            {
                                //直接创建表
                                sqliteHelper.ExecuteNonQuery(ddls[kv.Key]);
                            }
                            break;
                    }
                }
            }


            Load();
            return true;
        }

        /// <summary>
        /// 使数据库 只储存 从现在向前 N个月的数据
        /// </summary>
        /// <param name="month"></param>
        public void KeepDBSize(int month)
        {
            if (month < 1)
                month = 1;
            //最少保存上个月与这个月的数据
            DateTime del_time = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
            del_time = del_time.AddMonths(-month);


            List<string> sqls = new List<string>();

            //正常应该为
            //DELETE FROM ScanData WHERE Time < '2020-06-08'
            //
            //但 有INDEX 后 下面会更快
            //DELETE FROM ScanData WHERE ID <= (SELECT ID FROM ScanData WHERE Time < '2020-06-08' ORDER BY ID DESC LIMIT 1);


            //枚举全部TbXXX, 带有TimeAttribute 都删除
            foreach (IDBTable tb in DbSet)
            {
                sqls.Add(tb.GetDeleteSQL(del_time));
            }

            //把sqls里面为空的全部删除
            sqls.RemoveAll(sql => sql == null);
            if (sqls.Count == 0)
                return;
            int cnt = 0;
            foreach (var sql in sqls) {
                cnt += sqliteHelper.ExecuteNonQuery(sql);
            }
            if (cnt > 0) {
                //数据库压缩
                sqliteHelper.ExecuteNonQuery("VACUUM");
            }
        }
        
        /// <summary>
        /// 数据库按月备份，当前月不备份
        /// </summary>
        public void BackupDb() 
        {
            BackupBbInSize(10 * 12);
        }

        /// <summary>
        /// 数据库按月备份，当前月不备份, 只保存从现在开始向前的N个月
        /// </summary>
        /// <param name="month"></param>
        public void BackupBbInSize(int month) 
        {
            if (month < 12)
                month = 12;

            //保存当前月份除外的全部月份数据

            //保存到当前数据库所在的文件夹内

            //获取全部带来时间field 的表， 最早的那行时间
            var firstTimes = new List<DateTime>();

            foreach (IDBTable tb in DbSet)
            {
                if (string.IsNullOrEmpty(tb.TimeFieldName))
                    continue;

                string sql = $"SELECT {tb.TimeFieldName} FROM {tb.TableName} ORDER BY ID LIMIT 1";
                var ret = sqliteHelper.ExecuteScalar(sql);
                if (ret == null)
                    continue;//一行数据也没有

                if (!(ret is DateTime))
                    continue;//异常，返回的不是时间

                var firstTime = (DateTime)ret;
                firstTimes.Add(firstTime);
            }
            if (firstTimes.Count() == 0)
                return;//no datas

            DateTime curr_month = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
            int curr_total_month = curr_month.Year * 12 + curr_month.Month;
            int limit_total_month = curr_total_month - month;
            DateTime limit_month = new DateTime( limit_total_month/12, limit_total_month % 12, 1);
            DateTime start_month = firstTimes.Min();
            start_month = new DateTime(start_month.Year, start_month.Month, 1);
            if (start_month < limit_month)
                start_month = limit_month;

            if (start_month >= curr_month)
            {
                //最早的时间，都只是本月而已。不需要备份
                return;
            }

            DateTime i_month = start_month;
            //开始逐月备份
            //先检测是否已经备份了
            while (true)
            {

                if (IsNeedToBackup(i_month))//需要备份
                    BackupDb(i_month);

                i_month = i_month.AddMonths(1);
                if (i_month >= curr_month)
                {
                    //已经全部备份完成
                    break;
                }
            }
        }
        
        string GetBackupDbPath(DateTime month) 
        {
            string dirPath = Path.Combine(Path.GetDirectoryName(DBPath), $"{month:yyyy-MM}");
            string dbPath = Path.Combine(dirPath, Path.GetFileName(DBPath));
            return dbPath;
        }
        

        void BackupDb(DateTime month) 
        {
            string backup_dbPath = GetBackupDbPath(month);
            //把整个文件复制过去，再把其它月份的数据删除
            try
            {
                //先创建文件夹
                string dirPath = Path.GetDirectoryName(backup_dbPath);
                Directory.CreateDirectory(dirPath);

                //再复制
                File.Copy(DBPath, backup_dbPath);
            }
            catch {
                //失败
                return;
            }

            //删除其它月份的数据
            SQLiteHelper sqliteHelper2 = new SQLiteHelper();
            sqliteHelper2.ConnectionString = $"Data Source={backup_dbPath};Version=3;";


            List<string> sqls = new List<string>();
            DateTime next_month = month.AddMonths(1);
            //枚举全部TbXXX, 带有TimeAttribute 都删除
            foreach (IDBTable tb in DbSet)
            {
                if (string.IsNullOrEmpty(tb.TimeFieldName))
                    continue;
                
                string sql = $"DELETE FROM {tb.TableName}" +
                        $" WHERE {tb.TimeFieldName} < '{month:yyyy-MM}-01'" +
                        $" OR {tb.TimeFieldName}>='{next_month:yyyy-MM}-01'";

                sqls.Add(sql);
            }


            //把sqls里面为空的全部删除
            if (sqliteHelper2.QueryTran(sqls)) {
                //数据库压缩
                sqliteHelper2.ExecuteNonQuery("VACUUM");
            }

        }
        public bool IsNeedToBackup() 
        {
            DateTime curr_month = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
            DateTime pre_month = curr_month.AddMonths(-1);
            return IsNeedToBackup(pre_month);
        }
        /// <summary>
        /// 需要备份
        /// </summary>
        bool IsNeedToBackup(DateTime month) 
        {
            // 步骤1， 比步骤2时间要短

            //1. 检测是否有上一个月的备份文件路径
            string pre_dbPath = GetBackupDbPath(month);
            if (File.Exists(pre_dbPath))
            {
                //已经备份了
                return false;
            }
            //没有备份

            //2.检测当前的数据库，是否有上一个月的数据
            bool hasData = false;
            foreach (IDBTable tb in DbSet)
            {
                if (string.IsNullOrEmpty(tb.TimeFieldName))
                    continue;

                //检测是否有这个月份的数据
                //SELECT ID FROM Sample WHERE Time LIKE '2021-06%' LIMIT 1
                string sql = $"SELECT ID FROM {tb.TableName} WHERE {tb.TimeFieldName} LIKE '{month:yyyy-MM}%' LIMIT 1";
                var ret = sqliteHelper.ExecuteScalar(sql);
                if (ret == null)
                    continue;//没有这个月的数据

                //有数据
                hasData = true;
                break;
            }


            return hasData;//有数据，需要备份 ;
        }

        /// <summary>
        /// 限制备份的数据包数量，最少1年
        /// </summary>
        /// <param name="yeah"></param>
        public void KeepBackupSize(int month) 
        {
            if (month < 12)
                month = 12;
            int curr_month = DateTime.Now.Year*12 + DateTime.Now.Month;
            int del_month = curr_month - month;

            //枚举文件夹 全部 yyyy-MM 格式的数据
            Regex regex = new Regex(@"\A(\d\d\d\d)-(\d\d)\z");
            var dirInfo = new DirectoryInfo(Path.GetDirectoryName(DBPath));
            var dirInfos = dirInfo.GetDirectories();
            if (dirInfos == null || dirInfos.Count() == 0)
                return;//什么都没有
            
            foreach (var dinfo in dirInfos) {
                var match = regex.Match(dinfo.Name);
                if (match.Success) {
                    if (!int.TryParse(match.Groups[1].Value, out int y))
                        continue;
                    if (!int.TryParse(match.Groups[2].Value, out int m))
                        continue;
                    if (m > 12 || m < 1)
                        continue;//异常
                    if (y < 1900 || y > 2100)
                        continue;//异常

                    int total_m = y * 12 + m;
                    if (total_m > del_month)
                        continue;

                    //需要删除

                    //检查是否有数据库文件
                    string dbPath = GetBackupDbPath(new DateTime(y, m, 1));
                    try
                    {
                        File.Delete(dbPath);
                    }
                    catch {
                        //删除失败
                        continue;
                    }
                    try
                    {
                        //删除空目录，不为空，就会异常
                        Directory.Delete(dinfo.FullName);
                    }
                    catch {
                        continue;
                    }
                    
                }
            }
        }
    }
    
    
}