XdCxRhDW/XdCxRhDW.Repostory/EFContext/RHDWContext.cs

using SQLite.CodeFirst;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Configuration;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Core.Common;
using System.Data.Entity.Core.Common.CommandTrees;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Infrastructure.Interception;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Data.Entity.Validation;
using System.Data.SQLite;
using System.Data.SQLite.EF6;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows;
using XdCxRhDW.Entity;

namespace XdCxRhDW.Repostory
{
    class DbTableColumnInfo
    {
        public string name { get; set; }

        public string type { get; set; }

        public int notnull { get; set; }

        public int pk { get; set; }
    }

    public class DbContextBase : DbContext
    {
        public DbContextBase(string nameOrConnectionString)
            : base(nameOrConnectionString)
        {
        }
        public DbContextBase(DbConnection con, bool contextOwnsConnection)
            : base(con, contextOwnsConnection)
        {
        }

        /// <summary>
        /// 同步数据库表、字段、索引
        /// </summary>
        public void SyncDb()
        {
            SyncTable();
            SyncTableField();
            SyncIndex();
        }

        /// <summary>
        /// 手动创建组合索引(如果不存在)
        /// </summary>
        public void CreateCombinedIndex<TEntityType>(Expression<Func<TEntityType, object>> expr)
        {
            var props = ((NewExpression)expr.Body).Members.Select(p => p.Name).OrderBy(p => p).ToList();
            if (props.Count < 1) return;
            var name = string.Join("_", props);
            var tableName = typeof(TEntityType).GetCustomAttribute<TableAttribute>()?.Name;
            if (string.IsNullOrWhiteSpace(tableName))
                tableName = typeof(TEntityType).Name;
            string indexName = $"IX_{tableName}_{name}";
            StringBuilder sb = new StringBuilder();
            int idx = 0;
            foreach (var item in props)
            {
                if (idx < props.Count - 1)
                    sb.Append($"'{item}',");
                else
                    sb.Append($"'{item}'");
                idx++;
            }
            var columnStr = sb.ToString();
            var index = this.Database.SqlQuery<string>($"SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='{tableName}' and name='{indexName}'").FirstOrDefault();
            if (index == null)
                this.Database.ExecuteSqlCommand($"CREATE INDEX '{indexName}' ON {tableName} ({columnStr})");
        }

        /// <summary>
        /// 手动创建组合索引(如果不存在)
        /// </summary>
        public void CreateCombinedIndex(string tableName, string[] propNames)
        {
            if (string.IsNullOrWhiteSpace(tableName)) return;
            if (propNames == null || propNames.Length == 0) return;
            var props = propNames.OrderBy(p => p).ToList();
            var name = string.Join("_", props);
            string indexName = $"IX_{tableName}_{name}";
            StringBuilder sb = new StringBuilder();
            int idx = 0;
            foreach (var item in props)
            {
                if (idx < props.Count - 1)
                    sb.Append($"'{item}',");
                else
                    sb.Append($"'{item}'");
                idx++;
            }
            var columnStr = sb.ToString();
            var index = this.Database.SqlQuery<string>($"SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='{tableName}' and name='{indexName}'").FirstOrDefault();
            if (index == null)
                this.Database.ExecuteSqlCommand($"CREATE INDEX '{indexName}' ON {tableName} ({columnStr})");
        }

        void SyncIndex()
        {
            var tables = this.Database.SqlQuery<string>("select name from sqlite_master where type='table' and  name not like 'sqlite%'").ToList();
            var props = this.GetType().GetProperties();
            List<Type> entityTypes = new List<Type>();
            foreach (var prop in props)
            {
                bool isDbSet = prop.PropertyType.IsGenericType && typeof(DbSet<>) == prop.PropertyType.GetGenericTypeDefinition();
                if (isDbSet)
                {
                    var entityType = prop.PropertyType.GenericTypeArguments[0];
                    entityTypes.Add(entityType);
                }
            }
            foreach (var table in tables)
            {
                var res = this.Database.SqlQuery<DbTableColumnInfo>($"PRAGMA table_info([{table}])").ToList();
                var entityType = entityTypes.FirstOrDefault(p => p.Name == table);
                if (entityType == null) continue;

                //创建具有Index特性的索引列
                var entityProps = entityType.GetProperties().Where(p =>
                    p.CanRead
                    && p.Name.ToUpper() != "ID"
                    && p.CanWrite
                    && !p.GetMethod.IsVirtual
                    && p.GetCustomAttribute<IndexAttribute>() != null
                    && p.GetCustomAttribute<NotMappedAttribute>() == null);

                foreach (var prop in entityProps)
                {
                    var index = this.Database.SqlQuery<string>($"SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='{table}' and name='IX_{table}_{prop.Name}'").FirstOrDefault();
                    if (index == null)
                    {
                        this.Database.ExecuteSqlCommand($"CREATE INDEX 'IX_{table}_{prop.Name}' ON {table} ('{prop.Name}')");
                    }
                }

                //创建类上具有IndexCombined特性的组合索引
                var attrs = entityType.GetCustomAttributes<IndexCombinedAttribute>();
                foreach (var item in attrs)
                {
                    CreateCombinedIndex(table, item.Names);
                }
            }
        }
        void SyncTable()
        {
            var tables = this.Database.SqlQuery<string>("select name from sqlite_master where type='table' and  name not like 'sqlite%'").ToList();
            var props = this.GetType().GetProperties();
            Dictionary<string, Type> list = new Dictionary<string, Type>();
            foreach (var prop in props)
            {
                bool isDbSet = prop.PropertyType.IsGenericType && typeof(DbSet<>) == prop.PropertyType.GetGenericTypeDefinition();
                if (isDbSet)
                {
                    var entityType = prop.PropertyType.GenericTypeArguments[0];
                    var name = entityType.GetCustomAttribute<TableAttribute>()?.Name;
                    if (name == null)
                    {
                        name = entityType.Name;
                    }
                    list.Add(name, entityType);
                }
            }
            foreach (var item in list)
            {
                if (!tables.Contains(item.Key))
                {
                    var entityProps = item.Value.GetProperties().Where(p =>
                        p.CanRead
                        && p.CanWrite
                        && !p.GetMethod.IsVirtual
                        && p.GetCustomAttribute<NotMappedAttribute>() == null);
                    item.Value.GetProperties().Where(p => p.CanWrite);
                    StringBuilder sb = new StringBuilder();
                    sb.Append($"create table {item.Key}(ID integer primary key autoincrement");
                    foreach (var prop in entityProps)
                    {
                        if (prop.Name.ToLower() == "id") continue;
                        bool isNullable = prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>);
                        string typeStr = "";
                        var type = prop.PropertyType;
                        if (prop.PropertyType.IsGenericType)
                        {
                            type = prop.PropertyType.GenericTypeArguments[0];
                        }
                        if (type == typeof(string))
                        {
                            typeStr = "nvarchar";
                        }
                        else if (type == typeof(int) || type == typeof(long) || prop.PropertyType.IsEnum)
                        {
                            typeStr = "int";
                        }
                        else if (type == typeof(double) || type == typeof(float))
                        {
                            typeStr = "float";
                        }
                        else if (type == typeof(DateTime))
                        {
                            typeStr = "datetime";
                        }
                        sb.Append($",{prop.Name} {typeStr}");
                        if (!isNullable)
                        {
                            sb.Append(" not null");
                        }
                    }
                    sb.Append(")");
                    string createTableSql = sb.ToString();
                    Database.ExecuteSqlCommand(createTableSql);
                }
            }
        }
        void SyncTableField()
        {
            var tables = this.Database.SqlQuery<string>("select name from sqlite_master where type='table' and  name not like 'sqlite%'").ToList();
            var props = this.GetType().GetProperties();
            List<Type> entityTypes = new List<Type>();
            foreach (var prop in props)
            {
                bool isDbSet = prop.PropertyType.IsGenericType && typeof(DbSet<>) == prop.PropertyType.GetGenericTypeDefinition();
                if (isDbSet)
                {
                    var entityType = prop.PropertyType.GenericTypeArguments[0];
                    entityTypes.Add(entityType);
                }
            }
            foreach (var table in tables)
            {
                var res = this.Database.SqlQuery<DbTableColumnInfo>($"PRAGMA table_info([{table}])").ToList();
                var entityType = entityTypes.FirstOrDefault(p => p.Name == table);
                if (entityType == null) continue;
                var entityProps = entityType.GetProperties().Where(p =>
                    p.CanRead
                    && p.CanWrite
                    && !p.GetMethod.IsVirtual
                    && p.GetCustomAttribute<NotMappedAttribute>() == null);
                foreach (var prop in entityProps)
                {
                    var find = res.Find(p => p.name.ToLower() == prop.Name.ToLower());
                    if (find == null)
                    {
                        string typeStr = "";
                        var type = prop.PropertyType;
                        if (prop.PropertyType.IsGenericType)
                        {
                            type = prop.PropertyType.GenericTypeArguments[0];
                        }
                        if (type == typeof(string))
                        {
                            typeStr = "nvarchar";
                        }
                        else if (type == typeof(int) || type == typeof(long) || prop.PropertyType.IsEnum)
                        {
                            typeStr = "int";
                        }
                        else if (type == typeof(double) || type == typeof(float))
                        {
                            typeStr = "float";
                        }
                        else if (type == typeof(DateTime))
                        {
                            typeStr = "datetime";
                        }
                        else if (type == typeof(bool))
                        {
                            typeStr = "int";
                        }
                        bool isNullable = prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>);

                        string updateTableSql;
                        if (isNullable)
                            updateTableSql = $"alter table {table} add column {prop.Name} {typeStr}";
                        else
                        {
                            if (typeStr == "int" || typeStr == "float")
                                updateTableSql = $"alter table {table} add column {prop.Name} {typeStr} not null default 0";
                            else if (typeStr == "nvarchar")
                                updateTableSql = $"alter table {table} add column {prop.Name} {typeStr} not null default ''";
                            else if (typeStr == "datetime")
                                updateTableSql = $"alter table {table} add column {prop.Name} {typeStr} not null default '{DateTime.Now:yyyy-MM-dd HH:mm:ss}'";
                            else
                                updateTableSql = $"alter table {table} add column {prop.Name} {typeStr}";
                        }
                        this.Database.ExecuteSqlCommand(updateTableSql);
                    }
                }
            }
        }
    }

    public class RHDWLogContext : DbContextBase
    {
        public string DbFile;
        public RHDWLogContext() : base("LogDbCon") //配置使用的连接名
        {
            //|DataDirectory|在mvc等程序中代表了App_Data,在普通程序中代表程序根目录
            var dbFile = Database.Connection.ConnectionString.Replace("Data Source=", "").Replace("|DataDirectory|\\", "");
            this.DbFile = dbFile;
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            this.Database.Log = msg =>
            {
            };
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            modelBuilder.Configurations.AddFromAssembly(typeof(RHDWLogContext).Assembly);//自动加载Entity-Type
            var sqliteConnectionInitializer = new SqliteCreateDatabaseIfNotExists<RHDWLogContext>(modelBuilder);
            Database.SetInitializer(sqliteConnectionInitializer);
            base.OnModelCreating(modelBuilder);
        }
        public DbSet<LogRes> LogRes { set; get; }
    }
    /// <summary>
    /// 基础表上下文(id为int)
    /// </summary>
    public class RHDWContext : DbContextBase
    {
        public string DbFile;
        public static RHDWContext GetContext(bool readOnly = false)
        {
            string conStr = ConfigurationManager.ConnectionStrings["DbCon"].ConnectionString.Trim();
            if (conStr.EndsWith(";"))
                conStr = conStr.Substring(0, conStr.Length - 1);
            conStr = $"{conStr};Version=3";//Journal Mode=Off
            if (readOnly)
            {
                conStr = $"{conStr};Read Only=True";
            }
            SQLiteConnection con = new SQLiteConnection(conStr);
            if (!readOnly)
            {
                //con.Open();
                //var cmd = con.CreateCommand();
                //cmd.CommandText = "PRAGMA journal_mode=WAL";
                //cmd.ExecuteNonQuery();
                //con.Close();
            }
            var res = new RHDWContext(con);
            return res;

        }
        private RHDWContext(DbConnection con)
         : base(con, true)
        {
            var dbFile = Database.Connection.ConnectionString.Replace("Data Source=", "").Replace("|DataDirectory|\\", "");
            this.DbFile = dbFile;
        }
        //public RHDWContext() : base("DbCon") //配置使用的连接名
        //{
        //    //|DataDirectory|在mvc等程序中代表了App_Data,在普通程序中代表程序根目录
        //    this.DbFile = Database.Connection.ConnectionString.Replace("Data Source=", "").Replace("|DataDirectory|\\", "");
        //}

        public Task<List<T>> SqlQueryAsync<T>(string sql)
        {
            return this.Database.SqlQuery<T>(sql).ToListAsync();
        }
        public Task<T> SqlQueryOneAsync<T>(string sql)
        {
            return this.Database.SqlQuery<T>(sql).FirstOrDefaultAsync();

        }


        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            this.Database.Log = msg =>
            {
            };
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            modelBuilder.Configurations.AddFromAssembly(typeof(RHDWContext).Assembly);//自动加载Entity-Type
            var sqliteConnectionInitializer = new SqliteCreateDatabaseIfNotExists<RHDWContext>(modelBuilder);
            Database.SetInitializer(sqliteConnectionInitializer);
            base.OnModelCreating(modelBuilder);
        }
        public DbSet<XlInfo> XlInfos { set; get; }

        public DbSet<TaskInfo> TaskInfos { set; get; }

        public DbSet<TaskSig> TaskSigs { set; get; }

        public DbSet<TxInfo> TxInfos { get; set; }

        public DbSet<SatInfo> SatInfos { get; set; }

        public DbSet<FixedStation> FixedStation { get; set; }

        public DbSet<SigInfo> SigInfos { get; set; }

        public DbSet<SigDelay> SigDelays { get; set; }

        public DbSet<TargetInfo> TargetInfos { get; set; }

        public DbSet<SysSetings> SysSetings { get; set; }

        public DbSet<TaskRunnningInfo> TaskRunnningInfos { get; set; }
    }

    /// <summary>
    /// 分区表上下文(id为long)
    /// </summary>
    public class RHDWPartContext : DbContextBase
    {
        private string DbFile;
        public static RHDWPartContext GetContext(string dbFile, bool createDb = false, bool readOnly = false)
        {
            if (!File.Exists(dbFile) && !createDb)
            {
                return null;
            }
            string connectionString;
            if (readOnly)
            {
                connectionString = $@"Data Source={dbFile};RAGMA mode=ReadOnly;Journal=WAL";
            }
            else
            {
                connectionString = $@"Data Source={dbFile};Journal=WAL";
            }
            SQLiteConnection con = new SQLiteConnection(connectionString);
            return new RHDWPartContext(con);
        }
        public static RHDWPartContext GetContext(DateTime partTime, bool createDb = false, bool readOnly = false)
        {
            var dir = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "DbPart");
            var dayFile = Path.Combine(dir, $@"{partTime.Year}\{partTime:MMdd}.db");
            if (!File.Exists(dayFile) && !createDb)
            {
                return null;
            }
            string connectionString;
            if (readOnly)
            {
                connectionString = $@"Data Source=|DataDirectory|\DbPart\{partTime.Year}\{partTime:MMdd}.db;PRAGMA mode=ReadOnly;Journal=WAL";
            }
            else
            {
                connectionString = $@"Data Source=|DataDirectory|\DbPart\{partTime.Year}\{partTime:MMdd}.db;Journal=WAL";
            }
            SQLiteConnection con = new SQLiteConnection(connectionString);
            return new RHDWPartContext(con);
        }
        private RHDWPartContext(DbConnection con)
        : base(con, true)
        {
            var dbFile = Database.Connection.ConnectionString.Replace("Data Source=", "").Replace("|DataDirectory|\\", "");
            this.DbFile = dbFile;
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            this.Database.Log = msg =>
            {
            };
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            modelBuilder.Configurations.AddFromAssembly(typeof(RHDWPartContext).Assembly);
            var sqliteConnectionInitializer = new SqliteCreateDatabaseIfNotExists<RHDWPartContext>(modelBuilder);
            Database.SetInitializer(sqliteConnectionInitializer);
            base.OnModelCreating(modelBuilder);
        }

        public DbSet<StationRes> StationRes { get; set; }
        public DbSet<CxRes> CxRes { get; set; }
        public DbSet<CgRes> CgRes { get; set; }
        public DbSet<CgXgfRes> CgXgfRes { get; set; }
        public DbSet<PosRes> PosRes { get; set; }
        public DbSet<CheckRes> CheckRes { get; set; }
    }

    public class SqliteConfiguration : DbConfiguration
    {
        public SqliteConfiguration()
        {
            DbInterception.Add(new SqliteInterceptor());//拦截器
            SetProviderFactory("System.Data.SQLite", SQLiteFactory.Instance);
            SetProviderFactory("System.Data.SQLite.EF6", SQLiteProviderFactory.Instance);
            SetProviderServices("System.Data.SQLite", (DbProviderServices)SQLiteProviderFactory.Instance.GetService(typeof(DbProviderServices)));

        }
    }
}