WcsFramework/SqlSugar.BzTDengineCore/TDengine/DbMaintenance/TDengineDbMaintenance.cs

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace SqlSugar.BzTDengineCore
{
    public class TDengineDbMaintenance : DbMaintenanceProvider
    {
        #region DML

        protected override string GetViewInfoListSql => throw new NotImplementedException();

        protected override string GetDataBaseSql
        {
            get
            {
                return "show databases";
            }
        }

        protected override string GetColumnInfosByTableNameSql
        {
            get
            {
                throw new NotSupportedException("TDengineCode暂时不支持DbFirst等方法,还在开发");
            }
        }

        protected override string GetTableInfoListSql
        {
            get
            {
                var dt = GetSTables();
                List<string> sb = new List<string>();
                foreach (DataRow item in dt.Rows)
                {
                    sb.Add($" SELECT '{item["stable_name"].ObjToString().ToSqlFilter()}' AS NAME ");
                }
                var dt2 = GetTables();
                foreach (DataRow item in dt2.Rows)
                {
                    sb.Add($" SELECT '{item["table_name"].ObjToString().ToSqlFilter()}' AS NAME ");
                }
                var result = string.Join(" UNION ALL ", sb);
                if (string.IsNullOrEmpty(result))
                {
                    result = " SELECT 'NoTables' AS Name ";
                }
                return result;
            }
        }

        #endregion DML

        #region DDL

        protected override string CreateDataBaseSql
        {
            get
            {
                return "CREATE DATABASE IF NOT EXISTS {0} WAL_RETENTION_PERIOD 3600";
            }
        }

        protected override string AddPrimaryKeySql
        {
            get
            {
                return "ALTER TABLE {0} ADD PRIMARY KEY({2}) /*{1}*/";
            }
        }

        protected override string AddColumnToTableSql
        {
            get
            {
                return "ALTER TABLE {0} ADD COLUMN {1} {2}{3} {4} {5} {6}";
            }
        }

        protected override string AlterColumnToTableSql
        {
            get
            {
                return "alter table {0} MODIFY COLUMN {1} {2}{3} {4} {5} {6}";
            }
        }

        protected override string BackupDataBaseSql
        {
            get
            {
                return "mysqldump.exe  {0} -uroot -p > {1}  ";
            }
        }

        protected override string CreateTableSql
        {
            get
            {
                return "CREATE STABLE IF NOT EXISTS  {0}(\r\n{1} ) TAGS(" + SqlBuilder.GetTranslationColumnName("TagsTypeId") + " VARCHAR(20))";
            }
        }

        protected override string CreateTableColumn
        {
            get
            {
                return "{0} {1}{2} {3} {4} {5}";
            }
        }

        protected override string TruncateTableSql
        {
            get
            {
                return "TRUNCATE TABLE {0}";
            }
        }

        protected override string BackupTableSql
        {
            get
            {
                return "create table {0} as (select * from {1} limit {2} offset 0)";
            }
        }

        protected override string DropTableSql
        {
            get
            {
                return "DROP TABLE {0}";
            }
        }

        protected override string DropColumnToTableSql
        {
            get
            {
                return "ALTER TABLE {0} DROP COLUMN {1}";
            }
        }

        protected override string DropConstraintSql
        {
            get
            {
                return "ALTER TABLE {0} DROP CONSTRAINT {1}";
            }
        }

        protected override string RenameColumnSql
        {
            get
            {
                return "ALTER TABLE {0} RENAME {1} TO {2}";
            }
        }

        protected override string AddColumnRemarkSql => "comment on column {1}.{0} is '{2}'";

        protected override string DeleteColumnRemarkSql => "comment on column {1}.{0} is ''";

        protected override string IsAnyColumnRemarkSql
        { get { throw new NotSupportedException(); } }

        protected override string AddTableRemarkSql => "comment on table {0} is '{1}'";

        protected override string DeleteTableRemarkSql => "comment on table {0} is ''";

        protected override string IsAnyTableRemarkSql
        { get { throw new NotSupportedException(); } }

        protected override string RenameTableSql => "alter table  {0} to {1}";

        protected override string CreateIndexSql
        {
            get
            {
                return "CREATE {3} INDEX Index_{0}_{2} ON {0} ({1})";
            }
        }

        protected override string AddDefaultValueSql
        {
            get
            {
                return "ALTER TABLE {0} ALTER COLUMN {1} SET DEFAULT {2}";
            }
        }

        protected override string IsAnyIndexSql
        {
            get
            {
                return "  SELECT count(1) WHERE upper('{0}') IN ( SELECT upper(indexname) FROM pg_indexes )";
            }
        }

        protected override string IsAnyProcedureSql => throw new NotImplementedException();

        #endregion DDL

        #region Check

        protected override string CheckSystemTablePermissionsSql
        {
            get
            {
                return "SHOW DATABASES";
            }
        }

        #endregion Check

        #region Scattered

        protected override string CreateTableNull
        {
            get
            {
                return " ";
            }
        }

        protected override string CreateTableNotNull
        {
            get
            {
                return " ";
            }
        }

        protected override string CreateTablePirmaryKey
        {
            get
            {
                return "PRIMARY KEY";
            }
        }

        protected override string CreateTableIdentity
        {
            get
            {
                return "serial";
            }
        }

        #endregion Scattered

        #region Methods

        public override bool AddColumn(string tableName, DbColumnInfo columnInfo)
        {
            if (columnInfo.DbColumnName == "TagsTypeId")
            {
                return true;
            }
            tableName = SqlBuilder.GetTranslationTableName(tableName);
            var isAddNotNUll = columnInfo.IsNullable == false && columnInfo.DefaultValue.HasValue();
            if (isAddNotNUll)
            {
                columnInfo = Context.Utilities.TranslateCopy(columnInfo);
                columnInfo.IsNullable = true;
            }
            string sql = GetAddColumnSql(tableName, columnInfo);
            Context.Ado.ExecuteCommand(sql);
            return true;
        }

        public override List<DbTableInfo> GetViewInfoList(bool isCache = true)
        {
            return new List<DbTableInfo>();
        }

        public override bool CreateDatabase(string databaseName, string databaseDirectory = null)
        {
            var db = Context.CopyNew();
            db.Ado.Connection.ChangeDatabase("");
            var sql = CreateDataBaseSql;
            if (Context.CurrentConnectionConfig.ConnectionString.ToLower().Contains("config_us"))
            {
                sql += " PRECISION 'us'";
            }
            else if (Context.CurrentConnectionConfig.ConnectionString.ToLower().Contains("config_ns"))
            {
                sql += " PRECISION 'ns'";
            }
            db.Ado.ExecuteCommand(string.Format(sql, databaseName));
            return true;
        }

        public override List<string> GetIndexList(string tableName)
        {
            var sql = $"SELECT indexname, indexdef FROM pg_indexes WHERE upper(tablename) = upper('{tableName}')";
            return Context.Ado.SqlQuery<string>(sql);
        }

        public override List<string> GetProcList(string dbName)
        {
            var sql = $"SELECT proname FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid WHERE n.nspname = '{dbName}'";
            return Context.Ado.SqlQuery<string>(sql);
        }

        public override bool AddDefaultValue(string tableName, string columnName, string defaultValue)
        {
            return base.AddDefaultValue(SqlBuilder.GetTranslationTableName(tableName), SqlBuilder.GetTranslationTableName(columnName), defaultValue);
        }

        public override bool AddColumnRemark(string columnName, string tableName, string description)
        {
            tableName = SqlBuilder.GetTranslationTableName(tableName);
            string sql = string.Format(AddColumnRemarkSql, SqlBuilder.GetTranslationColumnName(columnName.ToLower(isAutoToLowerCodeFirst)), tableName, "");
            //Context.Ado.ExecuteCommand(sql);
            return false;
        }

        public override bool AddTableRemark(string tableName, string description)
        {
            tableName = SqlBuilder.GetTranslationTableName(tableName);
            return base.AddTableRemark(tableName, description);
        }

        public override bool UpdateColumn(string tableName, DbColumnInfo columnInfo)
        {
            tableName = SqlBuilder.GetTranslationTableName(tableName);
            var columnName = SqlBuilder.GetTranslationColumnName(columnInfo.DbColumnName);
            string sql = GetUpdateColumnSql(tableName, columnInfo);
            Context.Ado.ExecuteCommand(sql);
            var isnull = columnInfo.IsNullable ? " DROP NOT NULL " : " SET NOT NULL ";
            Context.Ado.ExecuteCommand(string.Format("alter table {0} alter {1} {2}", tableName, columnName, isnull));
            return true;
        }

        protected override string GetUpdateColumnSql(string tableName, DbColumnInfo columnInfo)
        {
            string columnName = SqlBuilder.GetTranslationColumnName(columnInfo.DbColumnName);
            tableName = SqlBuilder.GetTranslationTableName(tableName);
            string dataSize = GetSize(columnInfo);
            string dataType = columnInfo.DataType;
            //if (!string.IsNullOrEmpty(dataType))
            //{
            //    dataType = " type " + dataType;
            //}
            string nullType = "";
            string primaryKey = null;
            string identity = null;
            string result = string.Format(AlterColumnToTableSql, tableName, columnName, dataType, dataSize, nullType, primaryKey, identity);
            return result;
        }

        public override bool AddRemark(EntityInfo entity)
        {
            var db = Context;
            var columns = entity.Columns.Where(it => it.IsIgnore == false).ToList();

            foreach (var item in columns)
            {
                if (item.ColumnDescription != null)
                {
                    db.DbMaintenance.AddColumnRemark(item.DbColumnName, item.DbTableName, item.ColumnDescription);
                }
            }
            //table remak
            if (entity.TableDescription != null)
            {
                db.DbMaintenance.AddTableRemark(entity.DbTableName, entity.TableDescription);
            }
            return true;
        }

        public override bool CreateTable(string tableName, List<DbColumnInfo> columns, bool isCreatePrimaryKey = true)
        {
            if (columns.HasValue())
            {
                foreach (var item in columns)
                {
                    if (item.DbColumnName != null && item.DbColumnName.Equals("GUID", StringComparison.CurrentCultureIgnoreCase) && item.Length == 0)
                    {
                        item.Length = 10;
                    }
                }
            }
            string sql = GetCreateTableSql(tableName, columns);
            string primaryKeyInfo = null;
            if (columns.Any(it => it.IsPrimarykey) && isCreatePrimaryKey)
            {
                primaryKeyInfo = string.Format(", Primary key({0})", string.Join(",", columns.Where(it => it.IsPrimarykey).Select(it => SqlBuilder.GetTranslationColumnName(it.DbColumnName.ToLower(isAutoToLowerCodeFirst)))));
            }
            sql = sql.Replace("$PrimaryKey", primaryKeyInfo);
            Context.Ado.ExecuteCommand(sql);
            return true;
        }

        protected override string GetCreateTableSql(string tableName, List<DbColumnInfo> columns)
        {
            List<string> columnArray = new List<string>();
            Check.Exception(columns.IsNullOrEmpty(), "No columns found ");
            foreach (var item in columns.Where(x => x.DbColumnName != null))
            {
                string columnName = item.DbColumnName;
                string dataType = item.DataType;
                if (dataType == "varchar" && item.Length == 0)
                {
                    item.Length = 1;
                }
                //if (dataType == "uuid")
                //{
                //    item.Length = 50;
                //    dataType = "varchar";
                //}
                string dataSize = item.Length > 0 ? string.Format("({0})", item.Length) : null;
                //if (item.DecimalDigits > 0&&item.Length>0 && dataType?.ToLower()== "float")
                //{
                //    item.Length = 0;
                //    dataSize = $"({item.Length},{item.DecimalDigits})";
                //}
                //if (item.DecimalDigits > 0 && item.Length > 0 && dataType?.ToLower() ==  "double")
                //{
                //    dataSize = $"({item.Length},{item.DecimalDigits})";
                //}
                //if (item.DecimalDigits > 0 && item.Length > 0 && dataType?.ToLower() == "decimal")
                //{
                //    dataSize = $"({item.Length},{item.DecimalDigits})";
                //}
                //if (item.DecimalDigits == 0 && item.Length == 0 && dataType?.ToLower() == "float")
                //{
                //   dataType = $"FLOAT(18,4)";
                //}
                //if (item.DecimalDigits == 0 && item.Length == 0 && dataType?.ToLower() == "double")
                //{
                //    dataType = $"DOUBLE(18,4)";
                //}
                if (item.Length == 0 && dataType?.ToLower()?.IsIn("nchar", "varchar") == true)
                {
                    dataType = "VARCHAR(200)";
                }
                if (dataType?.ToLower()?.IsIn("float", "double") == true)
                {
                    dataSize = null;
                }
                string primaryKey = null;
                string addItem = string.Format(CreateTableColumn, SqlBuilder.GetTranslationColumnName(columnName.ToLower(isAutoToLowerCodeFirst)), dataType, dataSize, null, primaryKey, "");
                columnArray.Add(addItem);
            }
            string tableString = string.Format(CreateTableSql, SqlBuilder.GetTranslationTableName(tableName.ToLower(isAutoToLowerCodeFirst)), string.Join(",\r\n", columnArray));
            var childTableName = SqlBuilder.GetTranslationTableName(tableName.ToLower(isAutoToLowerCodeFirst));
            var stableName = SqlBuilder.GetTranslationTableName(tableName.ToLower(isAutoToLowerCodeFirst));
            //Context.Ado.ExecuteCommand(tableString);
            //var createChildSql = $"CREATE TABLE IF NOT EXISTS     {childTableName} USING {stableName} TAGS('default')";
            //Context.Ado.ExecuteCommand(createChildSql);
            return tableString;
        }

        public override bool IsAnyConstraint(string constraintName)
        {
            throw new NotSupportedException("PgSql IsAnyConstraint NotSupportedException");
        }

        public override bool BackupDataBase(string databaseName, string fullFileName)
        {
            Check.ThrowNotSupportedException("PgSql BackupDataBase NotSupported");
            return false;
        }

        public override List<DbColumnInfo> GetColumnInfosByTableName(string tableName, bool isCache = true)
        {
            var sql = $"select * from {SqlBuilder.GetTranslationColumnName(tableName)} where 1=2 ";
            List<DbColumnInfo> result = new List<DbColumnInfo>();
            DataTable dt = null;
            try
            {
                dt = Context.Ado.GetDataTable(sql);
            }
            catch (Exception)
            {
                sql = $"select * from `{tableName}` where 1=2 ";
                dt = Context.Ado.GetDataTable(sql);
            }
            foreach (DataColumn item in dt.Columns)
            {
                var addItem = new DbColumnInfo()
                {
                    DbColumnName = item.ColumnName,
                    DataType = item.DataType.Name
                };
                result.Add(addItem);
            }
            if (result.Count(it => it.DataType == "DateTime") == 1)
            {
                result.First(it => it.DataType == "DateTime").IsPrimarykey = true;
            }
            return result;
        }

        #endregion Methods

        #region Helper

        private bool isAutoToLowerCodeFirst
        {
            get
            {
                if (Context.CurrentConnectionConfig.MoreSettings == null) return true;
                else if (
                    Context.CurrentConnectionConfig.MoreSettings.PgSqlIsAutoToLower == false &&
                    Context.CurrentConnectionConfig.MoreSettings?.PgSqlIsAutoToLowerCodeFirst == false)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }

        private string GetSchema()
        {
            var schema = "public";
            if (System.Text.RegularExpressions.Regex.IsMatch(Context.CurrentConnectionConfig.ConnectionString.ToLower(), "searchpath="))
            {
                var regValue = System.Text.RegularExpressions.Regex.Match(Context.CurrentConnectionConfig.ConnectionString.ToLower(), @"searchpath\=(\w+)").Groups[1].Value;
                if (regValue.HasValue())
                {
                    schema = regValue;
                }
            }
            else if (System.Text.RegularExpressions.Regex.IsMatch(Context.CurrentConnectionConfig.ConnectionString.ToLower(), "search path="))
            {
                var regValue = System.Text.RegularExpressions.Regex.Match(Context.CurrentConnectionConfig.ConnectionString.ToLower(), @"search path\=(\w+)").Groups[1].Value;
                if (regValue.HasValue())
                {
                    schema = regValue;
                }
            }

            return schema;
        }

        private DataTable GetTables()
        {
            return Context.Ado.GetDataTable("SHOW TABLES");
        }

        private DataTable GetSTables()
        {
            return Context.Ado.GetDataTable("SHOW STABLES");
        }

        #endregion Helper
    }
}