private void GetFields(TableInfo table) { string _sqltext = $@"SELECT ORDINAL_POSITION ,COLUMN_NAME ,IS_NULLABLE ,COLUMN_TYPE ,(CASE WHEN COLUMN_TYPE IN ('tinyint(1)','char(36)') THEN COLUMN_TYPE ELSE DATA_TYPE END) AS DATA_TYPE ,COLUMN_COMMENT ,COALESCE( CASE WHEN DATA_TYPE IN ('tinyint','smallint','mediumint','int','bigint','bit','double','float','decimal') THEN NUMERIC_PRECISION WHEN DATA_TYPE IN ('date','time','year','timestamp','datetime') THEN DATETIME_PRECISION ELSE CHARACTER_MAXIMUM_LENGTH END ,0) AS LENGTEH ,COALESCE(NUMERIC_SCALE,0) AS NUMERIC_SCALE ,(EXTRA='auto_increment') as auto_increment from information_schema.`COLUMNS` where TABLE_SCHEMA='{table.Schema}' and TABLE_NAME='{table.Name}';"; _sqltext = string.Format(_sqltext, table.Schema, table.Name); dbContext.Execute.ExecuteDataReader(dr => { DbFieldInfo fi = new DbFieldInfo { Oid = Convert.ToInt32(dr["ORDINAL_POSITION"]), Name = dr["COLUMN_NAME"].ToString(), Length = Convert.ToInt64(dr["LENGTEH"].ToString()), NotNull = dr["IS_NULLABLE"].ToString() == "NO", Comment = dr["COLUMN_COMMENT"].ToString(), Numeric_scale = Convert.ToInt32(dr["NUMERIC_SCALE"].ToString()), DbType = dr["DATA_TYPE"].ToString(), AutoIncrement = Convert.ToBoolean(dr["auto_increment"]) }; fi.CsType = MysqlType.SwitchToCSharp(fi.DbType); if (!fi.NotNull && fi.CsType != "string" && fi.CsType != "byte[]" && fi.CsType != "JToken") { fi.RelType = $"{fi.CsType}?"; } else { fi.RelType = fi.CsType; } if ((fi.RelType == "string" && fi.Length != 0 && fi.Length != 255) || (fi.Numeric_scale > 0) || (MysqlType.ContrastType(fi.DbType) == null)) { fi.DbTypeFull = dr["COLUMN_TYPE"].ToString(); } table.Fields.Add(fi); }, CommandType.Text, _sqltext); if (table.Type == TableType.Table) { GetPrimarykey(table); } }
private void SerializeField(TableInfo table, Type type) { var properties = MyStagingUtils.GetDbFields(type); foreach (var pi in properties) { var fi = new DbFieldInfo(); fi.Name = pi.Name; var customAttributes = pi.GetCustomAttributes(); var genericAttrs = customAttributes.Select(f => f.GetType()).ToArray(); if (pi.PropertyType.Name == "Nullable`1") { fi.NotNull = false; fi.CsType = pi.PropertyType.GenericTypeArguments[0].Name; } else { fi.CsType = pi.PropertyType.Name; if (pi.PropertyType == typeof(string)) { fi.NotNull = genericAttrs.Where(f => f == typeof(RequiredAttribute) || f == typeof(PrimaryKeyAttribute)).FirstOrDefault() != null; } else { fi.NotNull = pi.PropertyType.IsValueType; } } fi.PrimaryKey = genericAttrs.Where(f => f == typeof(PrimaryKeyAttribute)).FirstOrDefault() != null; if (fi.PrimaryKey) { var pk = pi.GetCustomAttribute <PrimaryKeyAttribute>(); fi.AutoIncrement = pk.AutoIncrement; } var columnAttribute = customAttributes.Where(f => f.GetType() == typeof(ColumnAttribute)).FirstOrDefault(); if (columnAttribute != null) { var colAttribute = ((ColumnAttribute)columnAttribute); fi.DbType = fi.DbTypeFull = colAttribute.TypeName; if (colAttribute.TypeName != "char(36)" && colAttribute.TypeName != "tinyint(1)") { var zero = colAttribute.TypeName.IndexOf("("); if (zero > 0) { fi.DbType = colAttribute.TypeName.Substring(0, zero); } } } else { fi.DbTypeFull = GetFullDbType(fi); fi.DbType = MysqlType.GetDbType(fi.CsType); if (fi.DbType == "varchar" || fi.DbType == "char") { fi.DbTypeFull = $"{fi.DbType}(255)"; } } table.Fields.Add(fi); } }
private void DumpAlter(TableInfo newTable, TableInfo oldTable, ref StringBuilder sb) { var alterSql = $"ALTER TABLE {MyStagingUtils.GetTableName(newTable, ProviderType.MySql)}"; // 常规 foreach (var newFi in newTable.Fields) { var oldFi = oldTable.Fields.Where(f => f.Name == newFi.Name).FirstOrDefault(); var notNull = newFi.NotNull ? " NOT NULL" : ""; var realType = MysqlType.GetRealType(newFi); if (oldFi == null) { sb.AppendLine($"{alterSql} ADD COLUMN `{newFi.Name}` {realType} {notNull};"); } else if (oldFi.DbType != newFi.DbType || oldFi.NotNull != newFi.NotNull) { sb.AppendLine($"{alterSql} MODIFY COLUMN `{newFi.Name}` {realType}{notNull};"); } } // 移除旧字段 foreach (var oldFi in oldTable.Fields) { if (newTable.Fields.Where(f => f.Name == oldFi.Name).FirstOrDefault() == null) { sb.AppendLine($"{alterSql} DROP COLUMN `{oldFi.Name}`;"); } } // PRIMARY KEY var changed = PKChanged(oldTable, newTable); if (changed) { var newPk = newTable.Fields.Where(f => f.PrimaryKey).ToList(); if (newPk.Count > 0) { // 删除数据库约束 if (oldTable.Fields.Where(f => f.PrimaryKey).FirstOrDefault() != null) { var auto_increment = oldTable.Fields.Where(f => f.PrimaryKey && f.AutoIncrement).FirstOrDefault(); if (auto_increment != null) { sb.AppendLine($"{alterSql} MODIFY COLUMN `{auto_increment.Name}` {auto_increment.DbType};"); } sb.AppendLine($"{alterSql} DROP PRIMARY KEY;"); } // 增加实体约束 if (newPk.Count == 1) { var auto_increment = newPk[0].AutoIncrement ? " AUTO_INCREMENT" : ""; sb.AppendLine($"{alterSql} MODIFY {newPk[0].Name} {newPk[0].DbType} PRIMARY KEY{auto_increment};"); } else if (newPk.Count > 1) { var pks = string.Join(",", newPk.Select(f => "`" + f.Name + "`")); sb.AppendLine($"{alterSql} Add PRIMARY KEY({pks});"); } } } }