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});");
}
}
}
}
