hotline/src/Hotline.Repository.SqlSugar/Extensions/SqlSugarStartupExtensions.cs

using System;
using System.Collections;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Diagnostics;
using System.Linq.Dynamic.Core;
using System.Linq.Expressions;
using System.Reflection;
using System.Text.Encodings.Web;
using System.Text.Json;
using System.Text.RegularExpressions;
using System.Text.Unicode;
using Hotline.Share.Tools;
using Hotline.Users;
using Microsoft.AspNetCore.Builder;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Serilog;
using SqlSugar;
using XF.Domain.Entities;
using XF.Domain.Extensions;
using XF.Domain.Options;
using XF.Domain.Repository;
using XF.Domain.Repository.Events;
using XF.Utility.SequentialId;

namespace Hotline.Repository.SqlSugar.Extensions
{
    public static class SqlSugarStartupExtensions
    {
        public static void AddSqlSugar(this IServiceCollection services, IConfiguration configuration, string dbName = "Hotline")
        {
            //services.AddScoped<DatabaseEventDispatcher>();
            //多租户 new SqlSugarScope(List<ConnectionConfig>,db=>{});

            var slaveConnections = new List<SlaveConnectionConfig>();
            var slaveConnection1 = configuration.GetConnectionString("Hotline1");
            if (!string.IsNullOrEmpty(slaveConnection1))
                slaveConnections.Add(new SlaveConnectionConfig { ConnectionString = slaveConnection1 });

            SqlSugarScope sqlSugar = new SqlSugarScope(new ConnectionConfig()
            {
                DbType = DbType.PostgreSQL,
                ConnectionString = configuration.GetConnectionString(dbName),
                SlaveConnectionConfigs = slaveConnections,
                IsAutoCloseConnection = true,
                ConfigureExternalServices = new ConfigureExternalServices
                {
                    EntityService = (property, column) =>
                    {
                        var attributes = property.GetCustomAttributes(true); //get all attributes 

                        //if (attributes.Any(it => it is KeyAttribute))// by attribute set primarykey
                        //{
                        //    column.IsPrimarykey = true; //有哪些特性可以看 1.2 特性明细
                        //}
                        ////可以写多个，这边可以断点调试
                        //// if (attributes.Any(it => it is NotMappedAttribute))
                        ////{
                        ////    column.IsIgnore= true; 
                        ////}
                        //if (attributes.Any(it => it is DbNullableAttribute))
                        //{
                        //    column.IsNullable = true;
                        //}
                        //if (attributes.Any(it => it is DbJsonAttribute))
                        //{
                        //    column.DataType = "varchar(3000)";
                        //    column.IsJson = true;
                        //}
                        //if (attributes.Any(it => it is DbLengthAttribute))
                        //{
                        //    column.Length = (attributes.First(d => d is DbLengthAttribute) as DbLengthAttribute)?.MaxLength ?? 255;
                        //}
                        //if (attributes.Any(it => it is DbIgnoreAttribute))
                        //{
                        //    column.IsIgnore = true;
                        //}
                        //if (property.PropertyType.IsGenericType &&
                        //    property.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                        //{
                        //    column.IsNullable = true;
                        //}

                        if (column.PropertyName.ToLower() == "id" ||
                            attributes.Any(it => it is KeyAttribute)) //是id的设为主键
                        {
                            column.IsPrimarykey = true;
                            column.Length = 36;
                        }

                        //if (!column.DbColumnName.Contains("_"))
                        //    column.DbColumnName = UtilMethods.ToUnderLine(column.DbColumnName);//ToUnderLine驼峰转下划线

                        //column.ColumnDescription = (attributes.FirstOrDefault(d => d is DescriptionAttribute) as DescriptionAttribute)?.Description ?? string.Empty;

                        //统一设置 nullable等于isnullable=true
                        if (!column.IsPrimarykey && new NullabilityInfoContext().Create(property).WriteState is NullabilityState.Nullable)
                        {
                            column.IsNullable = true;
                        }
                    },
                    EntityNameService = (type, entity) =>
                    {
                        var attributes = type.GetCustomAttributes(true);
                        //if (attributes.Any(it => it is TableAttribute))
                        //{
                        //    entity.DbTableName = (attributes.First(it => it is TableAttribute) as TableAttribute).UserName;
                        //}

                        entity.DbTableName = entity.DbTableName.ToSnakeCase();

                        //if (!entity.DbTableName.Contains("_"))
                        //    entity.DbTableName = UtilMethods.ToUnderLine(entity.DbTableName);//ToUnderLine驼峰转下划线方法
                        if (attributes.Any(d => d is DescriptionAttribute))
                        {
                            entity.TableDescription =
                                (attributes.First(d => d is DescriptionAttribute) as DescriptionAttribute)
                                .Description;
                        }
                    }
                },
                MoreSettings = new ConnMoreSettings
                {
                    PgSqlIsAutoToLower = false,//增删查改支持驼峰表
                    PgSqlIsAutoToLowerCodeFirst = false, // 建表建驼峰表。5.1.3.30 
                }
            },
                db =>
                {
                    SetDbAop(db, services);
                    db.QueryFilter.AddTableFilter<ISoftDelete>(entity => !entity.IsDeleted);
                }
            );

            ISugarUnitOfWork<HotlineDbContext> context = new SugarUnitOfWork<HotlineDbContext>(sqlSugar);
            services.AddSingleton(context);

            InitDatabase(context, configuration);
        }

        private static void InitDatabase(ISugarUnitOfWork<HotlineDbContext> context, IConfiguration configuration)
        {
            var dbOptions = configuration.GetSection("DatabaseConfiguration").Get<DatabaseOptions>() ?? new DatabaseOptions();
            if (dbOptions.ApplyDbMigrations)
            {
                context.Db.DbMaintenance.CreateDatabase();

                var types = typeof(User).Assembly.GetTypes()
                    .Where(d => d.GetInterfaces().Any(x => x == typeof(ITable) && !d.IsAbstract))
                    .Distinct()
                    .ToArray();

                context.Db.CodeFirst.InitTables(types);//根据types创建表
            }

            if (dbOptions.ApplySeed)
            {
                var allTypes = AppDomain.CurrentDomain.GetAssemblies()
                    .SelectMany(d => d.GetTypes());

                var seedDataTypes = allTypes.Where(d => !d.IsInterface && !d.IsAbstract && d.IsClass
                                                        && d.HasImplementedOf(typeof(ISeedData<>)));

                foreach (var seedType in seedDataTypes)
                {
                    var instance = Activator.CreateInstance(seedType);

                    var hasDataMethod = seedType.GetMethod("HasData");
                    var seedData = ((IEnumerable)hasDataMethod?.Invoke(instance, null))?.Cast<object>();
                    if (seedData == null) continue;

                    var entityType = seedType.GetInterfaces().First().GetGenericArguments().First();

                    var entityInfo = context.Db.EntityMaintenance.GetEntityInfo(entityType);
                    if (entityInfo.Columns.Any(d => d.IsPrimarykey))
                    {
                        var storage = context.Db.StorageableByObject(seedData.ToList()).ToStorage();
                        storage.AsInsertable.ExecuteCommand();
                    }
                    else
                    {
                        // 无主键则只进行插入
                        if (!context.Db.Queryable(entityInfo.DbTableName, entityInfo.DbTableName).Any())
                            context.Db.InsertableByObject(seedData.ToList()).ExecuteCommand();
                    }
                }

                ////持久化需要持久化的runtime常量表
                //var constTypes = allTypes.Where(d=> !d.IsInterface && !d.IsAbstract && d.IsClass
                //                                    && d.HasImplementedOf(typeof(IConstTable<>)));

                //foreach (var constType in constTypes)
                //{
                //    var instance = Activator.CreateInstance(constType);

                //    var hasDataMethod = constType.GetMethod("GetData");
                //    var seedData = ((IEnumerable)hasDataMethod?.Invoke(instance, null))?.Cast<object>();
                //    if (seedData == null) continue;

                //    var entityType = constType.GetInterfaces().First().GetGenericArguments().First();

                //    var entityInfo = context.Db.EntityMaintenance.GetEntityInfo(entityType);
                //    if (entityInfo.Columns.Any(d => d.IsPrimarykey))
                //    {
                //        var storage = context.Db.StorageableByObject(seedData.ToList()).ToStorage();
                //        storage.AsInsertable.ExecuteCommand();
                //    }
                //    else
                //    {
                //        // 无主键则只进行插入
                //        if (!context.Db.Queryable(entityInfo.DbTableName, entityInfo.DbTableName).Any())
                //            context.Db.InsertableByObject(seedData.ToList()).ExecuteCommand();
                //    }
                //}

            }
        }

        #region private

        private static void SetDbAop(SqlSugarClient db, IServiceCollection services)
        {
            /***写AOP等方法***/
            db.Aop.OnLogExecuting = (sql, pars) =>
            {
                //Log.Warning($"sql参数：{JsonSerializer.Serialize(db.Ado.SqlStackTrace, new JsonSerializerOptions { Encoder = JavaScriptEncoder.Create(UnicodeRanges.BasicLatin, UnicodeRanges.CjkUnifiedIdeographs) })}");


                ////获取原生SQL推荐 5.1.4.63  性能OK
                //Log.Information(UtilMethods.GetNativeSql(sql, pars));

                //Log.Information("Sql: {0}", sql);
                //Log.Information("SqlParameters: {0}", string.Join(',', pars.Select(d => d.Value)));
            };
            db.Aop.OnError = (exp) =>//SQL报错
            {
                //获取原生SQL推荐 5.1.4.63  性能OK
                Log.Error(UtilMethods.GetNativeSql(exp.Sql, (SugarParameter[])exp.Parametres));
                //Log.Error(exp.InnerException.Message);
                //5.0.8.2 获取无参数化 SQL  对性能有影响，特别大的SQL参数多的，调试使用
                //UtilMethods.GetSqlString(DbType.SqlServer, exp.sql, exp.parameters)
            };
            //db.Aop.OnExecutingChangeSql = (sql, pars) => //可以修改SQL和参数的值
            //{
            //    //sql=newsql
            //    //foreach (var p in pars) //修改
            //    //{

            //    //}

            //    return new KeyValuePair<string, SugarParameter[]>(sql, pars);
            //};

            db.Aop.OnLogExecuted = (sql, p) =>
            {
                //执行时间超过1秒
                if (db.Ado.SqlExecutionTime.TotalSeconds > 1)
                {
                    //代码CS文件名
                    var fileName = db.Ado.SqlStackTrace.FirstFileName;
                    //代码行数
                    var fileLine = db.Ado.SqlStackTrace.FirstLine;
                    //方法名
                    var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName;
                    //db.Ado.SqlStackTrace.MyStackTraceList[1].xxx 获取上层方法的信息

                    Log.Warning("slow query ==> fileName: {fileName}, fileLine: {fileLine}, FirstMethodName: {FirstMethodName}",
                        fileName, fileLine, FirstMethodName);
                    Log.Warning(UtilMethods.GetNativeSql(sql, p));
                    Log.Warning("slow query totalSeconds: {sec}", db.Ado.SqlExecutionTime.TotalSeconds);
                }
            };

            db.Aop.DataChangesExecuted = (sql, entityInfo) =>
            {
                if (entityInfo.EntityColumnInfo.IsPrimarykey && entityInfo.OperationType == DataFilterType.UpdateByObject)
                {
                    services.BuildServiceProvider()
                    .GetService<DatabaseEventDispatcher>()?
                    .Dispatch(entityInfo.EntityValue, entityInfo.OperationType, false);
                }
                if (entityInfo.EntityColumnInfo.IsPrimarykey && entityInfo.OperationType == DataFilterType.InsertByObject)
                {
                    services.BuildServiceProvider()
                    .GetService<DatabaseEventDispatcher>()?
                    .Dispatch(entityInfo.EntityValue, entityInfo.OperationType, false);
                }
            };

            db.Aop.DataExecuting = (oldValue, entityInfo) =>
            {
                //inset生效
                if (entityInfo.PropertyName == "CreationTime" && entityInfo.OperationType == DataFilterType.InsertByObject)
                {
                    if (oldValue is DateTime createTime)
                    {
                        if (createTime == DateTime.MinValue)
                        {
                            entityInfo.SetValue(DateTime.Now);//修改CreateTime字段
                                                              //entityInfo有字段所有参数
                        }
                    }
                }
                //update生效        
                else if (entityInfo.PropertyName == "LastModificationTime" && entityInfo.OperationType == DataFilterType.UpdateByObject)
                {
                    entityInfo.SetValue(DateTime.Now);//修改UpdateTime字段
                }

                //根据当前列修改另一列 可以么写
                //if(当前列逻辑==XXX)
                //var properyDate = entityInfo.EntityValue.GetType().GetProperty("Date");
                //if(properyDate!=null)
                //properyDate.SetValue(entityInfo.EntityValue,1);

                else if (entityInfo.EntityColumnInfo.IsPrimarykey
                         && entityInfo.EntityColumnInfo.PropertyName.ToLower() == "id"
                         && entityInfo.OperationType == DataFilterType.InsertByObject
                         && oldValue is null) //通过主键保证只进一次事件
                {
                    //var propertyId = entityInfo.EntityValue.GetType().GetProperty("Id");
                    //if (propertyId is not null)
                    //{
                    //    var idValue = propertyId.GetValue(entityInfo.EntityValue);
                    //    if (idValue is null)
                    //        //这样每条记录就只执行一次 
                    //        entityInfo.SetValue(SequentialStringGenerator.Create());
                    //}
                    entityInfo.SetValue(SequentialStringGenerator.Create());

                }
            };

            //SetDeletedEntityFilter(db);
        }

        //private static void SetDeletedEntityFilter(SqlSugarClient db)
        //{
        //    var cacheKey = $"DbFilter:{db.CurrentConnectionConfig.ConfigId}:IsDeleted";
        //    var tableFilterItemList = db.DataCache.Get<List<TableFilterItem<object>>>(cacheKey);
        //    if (tableFilterItemList == null)
        //    {
        //        // 获取基类实体数据表
        //        var entityTypes = AppDomain.CurrentDomain.GetAssemblies()
        //            .SelectMany(d => d.GetTypes())
        //            .Where(d => !d.IsInterface
        //                        && !d.IsAbstract
        //                        && d.IsClass
        //                        && d.GetInterfaces().Any(x => x == typeof(ISoftDelete)));
        //        if (!entityTypes.Any()) return;

        //        var tableFilterItems = new List<TableFilterItem<object>>();
        //        foreach (var entityType in entityTypes)
        //        {
        //            if (entityType.GetProperty("IsDeleted") is null) continue;
        //            //// 排除非当前数据库实体
        //            //var tAtt = entityType.GetCustomAttribute<TenantAttribute>();
        //            //if ((tAtt != null && (string)db.CurrentConnectionConfig.ConfigId != tAtt.configId.ToString()) ||
        //            //    (tAtt == null && (string)db.CurrentConnectionConfig.ConfigId != SqlSugarConst.ConfigId))
        //            //    continue;

        //            var lambda = DynamicExpressionParser.ParseLambda(new[] {
        //                Expression.Parameter(entityType, "d") },
        //            typeof(bool),
        //            $"{nameof(SoftDeleteEntity.IsDeleted)} == @0", false);
        //            var tableFilterItem = new TableFilterItem<object>(entityType, lambda);
        //            tableFilterItems.Add(tableFilterItem);
        //            db.QueryFilter.Add(tableFilterItem);
        //        }
        //        db.DataCache.Add(cacheKey, tableFilterItems);
        //    }
        //    else
        //    {
        //        tableFilterItemList.ForEach(u =>
        //        {
        //            db.QueryFilter.Add(u);
        //        });
        //    }
        //}

        #endregion
    }
}