private void DumpTable(TableInfo table, ref StringBuilder sb)
{
sb.AppendLine($"CREATE TABLE {MyStagingUtils.GetTableName(table, ProviderType.MySql)}");
sb.AppendLine("(");
int length = table.Fields.Count;
List <string> keys = new List <string>();
for (int i = 0; i < length; i++)
{
var fi = table.Fields[i];
sb.AppendFormat(" `{0}` {1} {2}{3},\n",
fi.Name,
fi.DbTypeFull ?? fi.DbType,
fi.AutoIncrement ? "AUTO_INCREMENT" : "",
fi.PrimaryKey || fi.NotNull ? " NOT NULL" : ""
);
if (fi.PrimaryKey)
{
keys.Add(string.Format("`{0}`", fi.Name));
}
}
if (keys.Count() > 0)
{
sb.AppendLine($" PRIMARY KEY ({string.Join(", ", keys)})");
}
else
{
sb.Remove(sb.Length - 1, 1);
}
sb.AppendLine(");");
}
public void ToLowerPascal()
{
var field = "name";
var cast = MyStagingUtils.ToLowerPascal("Name");
Assert.Equal(field, cast);
}
public void GenerateMapping()
{
var contextName = $"{ Config.ProjectName }DbContext";
string _startup_file = Path.Combine(Config.OutputDir, $"{contextName}.cs");
using (StreamWriter writer = new StreamWriter(File.Create(_startup_file), System.Text.Encoding.UTF8))
{
writer.WriteLine($"using {Config.ProjectName}.Model;");
writer.WriteLine("using System;");
writer.WriteLine("using MyStaging.Core;");
writer.WriteLine("using MyStaging.Common;");
writer.WriteLine("using MyStaging.MetaData;");
writer.WriteLine("using Newtonsoft.Json.Linq;");
writer.WriteLine();
writer.WriteLine($"namespace {Config.ProjectName}");
writer.WriteLine("{");
writer.WriteLine($"\tpublic partial class {contextName} : DbContext");
writer.WriteLine("\t{");
writer.WriteLine($"\t\tpublic {contextName}(StagingOptions options) : base(options, ProviderType.MySql)");
writer.WriteLine("\t\t{");
writer.WriteLine("\t\t}");
writer.WriteLine();
foreach (var table in Tables)
{
var tableName = MyStagingUtils.ToUpperPascal(table.Name);
writer.WriteLine($"\t\tpublic DbSet<{tableName}> {tableName} {{ get; set; }}");
}
writer.WriteLine("\t}"); // class end
writer.WriteLine("}"); // namespace end
}
}
/// <summary>
/// 将当前更改保存到数据库
/// </summary>
/// <returns></returns>
public T SaveChange()
{
DeExpression();
CheckNotNull.NotEmpty(setList, "Fields to be updated must be provided!");
CheckNotNull.NotEmpty(WhereConditions, "The update operation must specify where conditions!");
this.ToSQL();
this.CommandText += " RETURNING *;";
var properties = MyStagingUtils.GetDbFields(typeof(T));
using var reader = dbContext.ByMaster().Execute.ExecuteDataReader(CommandType.Text, CommandText, this.Parameters.ToArray());
try
{
reader.Read();
T obj = (T)Activator.CreateInstance(typeof(T));
foreach (var pi in properties)
{
var value = reader[pi.Name];
if (value != DBNull.Value)
{
pi.SetValue(obj, value);
}
}
return(obj);
}
finally
{
Clear();
}
}
public static DynamicBuilder <T> CreateBuilder(IDataRecord dr)
{
DynamicBuilder <T> dynamicBuilder = new DynamicBuilder <T>();
dynamicBuilder.handler = (Load)MyStagingUtils.CreateDynamicDelegate(typeof(T), dr, typeof(Load));
return(dynamicBuilder);
}
public string ToSQL()
{
if (this.models.Count == 0)
{
throw new ArgumentOutOfRangeException("No items.");
}
Parameters.Clear();
string tableName = MyStagingUtils.GetMapping(typeof(T), ProviderType.MySql);
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append($"INSERT INTO {tableName}");
string fieldsName = string.Empty;
var properties = MyStagingUtils.GetDbFields(typeof(T));
foreach (var p in properties)
{
fieldsName += $"`{p.Name}`,";
}
fieldsName = fieldsName.Remove(fieldsName.Length - 1, 1);
sqlBuilder.Append($"({fieldsName}) VALUES ");
for (int i = 0; i < models.Count; i++)
{
string valueString = string.Empty;
foreach (var pi in properties)
{
var paramName = $"@{pi.Name}_{i}";
var value = pi.GetValue(models[i]);
var pk = pi.GetCustomAttribute <PrimaryKeyAttribute>();
var hasPK = pk != null;
if (hasPK && pk.AutoIncrement)
{
valueString += "default,";
}
else
{
valueString += paramName + ",";
if (hasPK || defaultValueField.ContainsKey(pi.Name.ToLower()))
{
if (value == null || value.Equals(Guid.Empty) || zeroTime.Equals(value))
{
value = GetDefaultValue(pi);
}
}
Parameters.Add(new MySqlParameter(paramName, value));
}
}
valueString = valueString.Remove(valueString.Length - 1, 1);
sqlBuilder.Append($"({valueString}),");
}
sqlBuilder.Remove(sqlBuilder.Length - 1, 1);
sqlBuilder.Append(";");
CommandText = sqlBuilder.ToString();
return(CommandText);
}
public T Add(T model)
{
this.models.Add(model);
this.ToSQL();
CommandText += " RETURNING *;";
var properties = MyStagingUtils.GetDbFields(typeof(T));
using var reader = dbContext.ByMaster().Execute.ExecuteDataReader(CommandType.Text, CommandText, this.Parameters.ToArray());
try
{
reader.Read();
T obj = (T)Activator.CreateInstance(typeof(T));
foreach (var pi in properties)
{
var value = reader[pi.Name];
if (value != DBNull.Value)
{
pi.SetValue(obj, value);
}
}
return(obj);
}
finally
{
this.Clear();
}
}
/// <summary>
/// 重写方法
/// </summary>
/// <returns></returns>
public override string ToSQL()
{
string tableName = MyStagingUtils.GetMapping(typeof(T), ProviderType.PostgreSQL);
this.CommandText = $"UPDATE {tableName} a SET {string.Join(",", this.setList)} {"WHERE " + string.Join("\nAND ", WhereConditions)}";
return(this.CommandText);
}
/// <summary>
/// 重写方法
/// </summary>
/// <returns></returns>
public override string ToSQL()
{
string tableName = MyStagingUtils.GetMapping(typeof(T), ProviderType.MySql);
this.CommandText = $"DELETE FROM {tableName} {"WHERE " + string.Join("\nAND ", WhereConditions)};";
return(this.CommandText);
}
private void DumpAlter(TableInfo newTable, TableInfo oldTable, ref StringBuilder sb)
{
var alterSql = $"ALTER TABLE {MyStagingUtils.GetTableName(newTable, ProviderType.PostgreSQL)}";
// 常规
foreach (var newFi in newTable.Fields)
{
var oldFi = oldTable.Fields.Where(f => f.Name == newFi.Name).FirstOrDefault();
var notNull = newFi.NotNull ? "NOT NULL" : "NULL";
var realType = newFi.DbTypeFull ?? newFi.DbType;
if (oldFi == null)
{
sb.AppendLine($"{alterSql} ADD \"{newFi.Name}\" {realType};");
sb.AppendLine($"{alterSql} MODIFY \"{newFi.Name}\" {realType} {notNull};");
}
else
{
if (oldFi.DbTypeFull != newFi.DbTypeFull)
{
sb.AppendLine($"{alterSql} ALTER \"{newFi.Name}\" TYPE {realType};");
}
if (oldFi.NotNull != newFi.NotNull)
{
sb.AppendLine($"{alterSql} MODIFY \"{newFi.Name}\" {realType} {notNull};");
}
}
}
// 移除旧字段
foreach (var oldFi in oldTable.Fields)
{
var newFi = newTable.Fields.Where(f => f.Name == oldFi.Name).FirstOrDefault();
if (newFi == null)
{
sb.AppendLine($"{alterSql} DROP COLUMN \"{oldFi.Name}\";");
}
}
// 检查旧约束
foreach (var c in oldTable.Constraints)
{
// PK
var constraint = newTable.Fields.Where(f => f.Name == c.Field && f.PrimaryKey).FirstOrDefault();
if (constraint == null)
{
sb.AppendLine($"{alterSql} DROP CONSTRAINT {c.Name};");
}
// SEQ
var seq = oldTable.Fields.Where(f => f.Name == c.Field && f.AutoIncrement).FirstOrDefault();
if (seq != null)
{
// 旧 increment 在新的同步中被删除
if (newTable.Fields.Where(f => f.Name == seq.Name && f.AutoIncrement).FirstOrDefault() == null)
{
var indexOf = seq.ColumnDefault.IndexOf("'") + 1;
var lastIndexOf = seq.ColumnDefault.LastIndexOf("'");
var seqName = seq.ColumnDefault[indexOf..lastIndexOf];
public void GetMemberName()
{
var source = new UserModel
{
Id = 1,
Age = 18,
};
UserModel target = new UserModel();
var memberName = MyStagingUtils.GetMemberName <UserModel, int>(f => f.Id);
Assert.Equal("Id", memberName);
}
public void CopyProperty()
{
var source = new UserModel
{
Id = 1,
Age = 18,
};
UserModel target = new UserModel();
MyStagingUtils.CopyProperty(target, source);
Assert.Equal(source.Id, target.Id);
}
public void GetMapping()
{
var mysqlName = "`mystaging`.`user`";
var mysql = MyStagingUtils.GetMapping(typeof(UserModel), Metadata.ProviderType.MySql);
Assert.Equal(mysqlName, mysql);
var pgsqlName = "\"mystaging\".\"user\"";
var pgsql = MyStagingUtils.GetMapping(typeof(UserModel), Metadata.ProviderType.PostgreSQL);
Assert.Equal(pgsqlName, pgsql);
}
public void Create()
{
string _classname = MyStagingUtils.ToUpperPascal(this.table.Name);
string _fileName = $"{config.ModelPath}/{_classname}.cs";
using StreamWriter writer = new StreamWriter(File.Create(_fileName), System.Text.Encoding.UTF8);
writer.WriteLine("using System;");
writer.WriteLine("using System.Linq;");
writer.WriteLine("using Newtonsoft.Json;");
writer.WriteLine("using Newtonsoft.Json.Linq;");
writer.WriteLine("using MySql.Data.Types;");
writer.WriteLine("using System.ComponentModel.DataAnnotations.Schema;");
writer.WriteLine("using System.ComponentModel.DataAnnotations;");
writer.WriteLine("using MyStaging.DataAnnotations;");
writer.WriteLine();
writer.WriteLine($"namespace {config.ProjectName}.Model");
writer.WriteLine("{");
writer.WriteLine($"\t[Table(name: \"{this.table.Name}\", Schema = \"{table.Schema}\")]");
writer.WriteLine($"\tpublic partial class {_classname}");
writer.WriteLine("\t{");
foreach (var fi in table.Fields)
{
if (!string.IsNullOrEmpty(fi.Comment))
{
writer.WriteLine("\t\t/// <summary>");
writer.WriteLine($"\t\t/// {fi.Comment}");
writer.WriteLine("\t\t/// </summary>");
}
var autoincrement = fi.AutoIncrement ? "(AutoIncrement = true)" : "";
if (fi.PrimaryKey)
{
writer.WriteLine($"\t\t[PrimaryKey{autoincrement}]");
}
if (fi.NotNull && fi.RelType == "string" && !fi.PrimaryKey)
{
writer.WriteLine("\t\t[Required]");
}
if (!string.IsNullOrEmpty(fi.DbTypeFull))
{
writer.WriteLine($"\t\t[Column(TypeName = \"{fi.DbTypeFull}\")]");
}
writer.WriteLine($"\t\tpublic {fi.RelType} {fi.Name} {{ get; set; }}");
}
writer.WriteLine("\t}");
writer.WriteLine("}");
writer.Flush();
}
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();
var pk = pi.GetCustomAttribute <PrimaryKeyAttribute>();
fi.PrimaryKey = pk != null;
if (fi.PrimaryKey)
{
fi.AutoIncrement = pk.AutoIncrement;
}
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 = fi.PrimaryKey || genericAttrs.Where(f => f == typeof(RequiredAttribute)).FirstOrDefault() != null;
}
else
{
fi.NotNull = pi.PropertyType.IsValueType;
}
}
var columnAttribute = customAttributes.Where(f => f.GetType() == typeof(ColumnAttribute)).FirstOrDefault();
if (columnAttribute != null)
{
var colAttribute = ((ColumnAttribute)columnAttribute);
fi.DbType = fi.DbTypeFull = colAttribute.TypeName;
}
else
{
fi.DbType = PgsqlType.GetDbType(fi.CsType.Replace("[]", ""));
fi.DbTypeFull = GetFullDbType(fi);
}
fi.IsArray = fi.CsType.Contains("[]");
table.Fields.Add(fi);
}
}
private string CreateName(string separator = "")
{
var tableName = MyStagingUtils.ToUpperPascal(table.Name);
string className;
if (table.Schema == "public")
{
className = tableName;
}
else
{
className = $"{MyStagingUtils.ToUpperPascal(table.Schema)}{separator}{tableName}";
}
return(className);
}
public void GetTableName()
{
var table = new TableInfo
{
Schema = "mystaging",
Name = "user"
};
var mysqlName = "`mystaging`.`user`";
var mysql = MyStagingUtils.GetTableName(table, Metadata.ProviderType.MySql);
Assert.Equal(mysqlName, mysql);
var pgsqlName = "\"mystaging\".\"user\"";
var pgsql = MyStagingUtils.GetTableName(table, Metadata.ProviderType.PostgreSQL);
Assert.Equal(pgsqlName, pgsql);
}
private void DumpTable(TableInfo table, ref StringBuilder sb)
{
var tableName = MyStagingUtils.GetTableName(table, ProviderType.PostgreSQL);
sb.AppendLine($"CREATE TABLE {tableName}");
sb.AppendLine("(");
int length = table.Fields.Count;
for (int i = 0; i < length; i++)
{
var fi = table.Fields[i];
sb.AppendFormat(" \"{0}\" {1}{2} {3} {4} {5}",
fi.Name,
fi.DbTypeFull ?? fi.DbType,
fi.IsArray ? "[]" : "",
fi.PrimaryKey ? "PRIMARY KEY" : "",
fi.PrimaryKey || fi.NotNull ? "NOT NULL" : "NULL",
(i + 1 == length) ? "" : ","
);
sb.AppendLine();
}
sb.AppendLine(")");
sb.AppendLine("WITH (OIDS=FALSE);");
// SEQ
foreach (var fi in table.Fields)
{
if (!fi.AutoIncrement)
{
continue;
}
var seqName = $"{ table.Name }_{ fi.Name}_seq";
sb.AppendLine();
sb.AppendLine($"--{seqName} SEQUENCE");
sb.AppendLine($"ALTER TABLE {tableName} ALTER COLUMN {fi.Name} SET DEFAULT null;");
sb.AppendLine($"DROP SEQUENCE IF EXISTS {seqName};");
sb.AppendLine($"CREATE SEQUENCE {seqName} START WITH 1;");
sb.AppendLine($"ALTER TABLE {tableName} ALTER COLUMN {fi.Name} SET DEFAULT nextval('{seqName}'::regclass);");
sb.AppendLine("-- SEQUENCE END");
}
}
public T Add(T model)
{
this.models.Add(model);
this.ToSQL();
var properties = MyStagingUtils.GetDbFields(typeof(T));
// 检查自增
PropertyInfo autoIncrement = null;
foreach (var pi in properties)
{
var pk = pi.GetCustomAttribute <PrimaryKeyAttribute>();
if (pk != null && pk.AutoIncrement)
{
autoIncrement = pi;
break;
}
}
try
{
if (autoIncrement != null)
{
this.CommandText += "\n SELECT LAST_INSERT_ID();";
}
using var reader = dbContext.ByMaster().Execute.ExecuteDataReader(CommandType.Text, CommandText, this.Parameters.ToArray());
if (autoIncrement != null)
{
reader.Read();
var value = reader[0];
value = Convert.ChangeType(value, autoIncrement.PropertyType);
autoIncrement.SetValue(model, value);
}
return(model);
}
finally
{
this.Clear();
}
}
/// <summary> /// 对查询进行求平均值 /// </summary> /// <param name="selector">字段选择器</param> /// <typeparam name="TSource">查询目标对象</typeparam> /// <typeparam name="TResult">接受查询结果类型</typeparam> /// <returns></returns> public TResult Avg <TSource, TResult>(Expression <Func <TSource, TResult> > selector) => Avg <TResult>(MyStagingUtils.GetMemberName(selector));
private void DumpAlter(TableInfo newTable, TableInfo oldTable, ref StringBuilder sb)
{
var alterSql = $"ALTER TABLE {MyStagingUtils.GetTableName(newTable, ProviderType.PostgreSQL)}";
// 常规
foreach (var newFi in newTable.Fields)
{
var oldFi = oldTable.Fields.Where(f => f.Name == newFi.Name).FirstOrDefault();
var notNull = newFi.NotNull ? "NOT NULL" : "NULL";
var realType = newFi.DbTypeFull ?? newFi.DbType;
if (oldFi == null)
{
sb.AppendLine($"{alterSql} ADD \"{newFi.Name}\" {realType};");
sb.AppendLine($"{alterSql} MODIFY \"{newFi.Name}\" {realType} {notNull};");
}
else
{
if (oldFi.DbTypeFull != newFi.DbTypeFull)
{
sb.AppendLine($"{alterSql} ALTER \"{newFi.Name}\" TYPE {realType};");
}
if (oldFi.NotNull != newFi.NotNull)
{
sb.AppendLine($"{alterSql} MODIFY \"{newFi.Name}\" {realType} {notNull};");
}
}
}
// 移除旧字段
foreach (var oldFi in oldTable.Fields)
{
var newFi = newTable.Fields.Where(f => f.Name == oldFi.Name).FirstOrDefault();
if (newFi == null)
{
sb.AppendLine($"{alterSql} DROP COLUMN \"{oldFi.Name}\";");
}
}
// 检查旧约束
foreach (var c in oldTable.Constraints)
{
// PK
var constraint = newTable.Fields.Where(f => f.Name == c.Field && f.PrimaryKey).FirstOrDefault();
if (constraint == null)
{
sb.AppendLine($"{alterSql} DROP CONSTRAINT {c.Name};");
}
// SEQ
var seq = oldTable.Fields.Where(f => f.Name == c.Field && f.AutoIncrement).FirstOrDefault();
if (seq != null)
{
// 旧 increment 在新的同步中被删除
if (newTable.Fields.Where(f => f.Name == seq.Name && f.AutoIncrement).FirstOrDefault() == null)
{
var indexOf = seq.ColumnDefault.IndexOf("'") + 1;
var lastIndexOf = seq.ColumnDefault.LastIndexOf("'");
var seqName = seq.ColumnDefault.Substring(indexOf, lastIndexOf - indexOf);
sb.AppendLine($"{alterSql} ALTER COLUMN {seq.Name} SET DEFAULT null;");
sb.AppendLine($"DROP SEQUENCE IF EXISTS {seqName};");
}
}
}
// 检查新约束
foreach (var fi in newTable.Fields)
{
if (!fi.PrimaryKey)
{
continue;
}
// PK
var constraint = oldTable.Constraints.Where(f => f.Field == fi.Name).FirstOrDefault();
if (constraint == null)
{
sb.AppendLine($"{alterSql} ADD CONSTRAINT pk_{newTable.Name} PRIMARY KEY({fi.Name});");
}
// SEQ
if (fi.AutoIncrement)
{
if (oldTable.Fields.Where(f => f.Name == fi.Name && f.AutoIncrement).FirstOrDefault() == null)
{
var seqName = $"{ newTable.Name }_{ fi.Name}_seq";
sb.AppendLine($"CREATE SEQUENCE {seqName} START WITH 1;");
sb.AppendLine($"ALTER TABLE \"{newTable.Schema}\".\"{newTable.Name}\" ALTER COLUMN \"{fi.Name}\" SET DEFAULT nextval('{seqName}'::regclass);");
}
}
}
}
public IUpdateBuilder <T> SetValue <TResult>(Expression <Func <T, TResult> > selector, object value)
{
return(SetValue(MyStagingUtils.GetMemberName <T, TResult>(selector), value));
}
public void CodeFirst(ProjectConfig config)
{
Initialize(config);
StringBuilder sb = new StringBuilder();
List <TableInfo> tables = new List <TableInfo>();
var fileName = config.ProjectName + ".dll";
var dir = System.IO.Directory.GetCurrentDirectory();
var providerFile = System.IO.Directory.GetFiles(dir, fileName, SearchOption.AllDirectories).FirstOrDefault();
if (string.IsNullOrEmpty(providerFile))
{
throw new FileNotFoundException($"在 {dir} 搜索不到文件 {fileName}");
}
var types = Assembly.LoadFrom(providerFile).GetTypes();
List <TableInfo> entitys = new List <TableInfo>();
foreach (var t in types)
{
var tableAttribute = t.GetCustomAttribute <TableAttribute>();
if (tableAttribute == null)
{
continue;
}
entitys.Add(new TableInfo
{
Name = tableAttribute.Name,
Schema = tableAttribute.Schema,
EntityType = t
});
}
foreach (var ent in entitys)
{
SerializeField(ent, ent.EntityType);
var table = Tables.Where(f => f.Schema == ent.Schema && f.Name == ent.Name).FirstOrDefault();
if (table == null) // CREATE
{
DumpTable(ent, ref sb);
}
else // ALTER
{
DumpAlter(ent, table, ref sb);
}
}
// 删除实体
foreach (var table in Tables)
{
if (entitys.Where(f => f.Schema == table.Schema && f.Name == table.Name).FirstOrDefault() == null)
{
sb.AppendLine($"DROP TABLE {MyStagingUtils.GetTableName(table, ProviderType.MySql)};");
}
}
var sql = sb.ToString();
if (string.IsNullOrEmpty(sql))
{
Console.WriteLine("数据模型没有可执行的更改.");
}
else
{
Console.WriteLine("------------------SQL------------------");
Console.WriteLine(sql);
Console.WriteLine("------------------SQL END------------------");
dbContext.Execute.ExecuteNonQuery(CommandType.Text, sql);
}
}
public void GetDbFields()
{
var pis = MyStagingUtils.GetDbFields(typeof(UserModel));
Assert.Equal(10, pis.Count);
}
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});");
}
}
}
}
public void GenerateMapping()
{
string _sqltext = @"
select a.oid,a.typname,b.nspname from pg_type a
INNER JOIN pg_namespace b on a.typnamespace = b.oid
where a.typtype = 'e' order by oid asc";
List <EnumTypeInfo> enums = new List <EnumTypeInfo>();
dbContext.Execute.ExecuteDataReader(dr =>
{
enums.Add(new EnumTypeInfo()
{
Oid = Convert.ToInt32(dr["oid"]),
TypeName = dr["typname"].ToString(),
NspName = dr["nspname"].ToString()
});
}, System.Data.CommandType.Text, _sqltext);
if (enums.Count > 0)
{
string _fileName = Path.Combine(Config.ModelPath, "_Enums.cs");
using StreamWriter writer = new StreamWriter(File.Create(_fileName), System.Text.Encoding.UTF8);
writer.WriteLine("using System;");
writer.WriteLine();
writer.WriteLine($"namespace {Config.ProjectName}.Model");
writer.WriteLine("{");
for (int i = 0; i < enums.Count; i++)
{
var item = enums[i];
writer.WriteLine($"\tpublic enum {item.TypeName}");
writer.WriteLine("\t{");
string sql = $"select oid,enumlabel from pg_enum WHERE enumtypid = {item.Oid} ORDER BY oid asc";
dbContext.Execute.ExecuteDataReader(dr =>
{
string c = i < enums.Count ? "," : "";
writer.WriteLine($"\t\t{dr["enumlabel"]}{c}");
}, CommandType.Text, sql);
writer.WriteLine("\t}");
}
writer.WriteLine("}");
}
var contextName = $"{ Config.ProjectName }DbContext";
string _startup_file = Path.Combine(Config.OutputDir, $"{contextName}.cs");
using (StreamWriter writer = new StreamWriter(File.Create(_startup_file), System.Text.Encoding.UTF8))
{
writer.WriteLine($"using {Config.ProjectName}.Model;");
writer.WriteLine("using System;");
writer.WriteLine("using Npgsql;");
writer.WriteLine("using MyStaging.Core;");
writer.WriteLine("using MyStaging.Common;");
writer.WriteLine("using MyStaging.MetaData;");
writer.WriteLine("using Newtonsoft.Json.Linq;");
writer.WriteLine();
writer.WriteLine($"namespace {Config.ProjectName}");
writer.WriteLine("{");
writer.WriteLine($"\tpublic partial class {contextName} : DbContext");
writer.WriteLine("\t{");
writer.WriteLine($"\t\tpublic {contextName}(StagingOptions options) : base(options, ProviderType.PostgreSQL)");
writer.WriteLine("\t\t{");
writer.WriteLine("\t\t}");
writer.WriteLine();
writer.WriteLine($"\t\tstatic {contextName}()");
writer.WriteLine("\t\t{");
writer.WriteLine("\t\t\tType[] jsonTypes = { typeof(JToken), typeof(JObject), typeof(JArray) };");
writer.WriteLine("\t\t\tNpgsqlNameTranslator translator = new NpgsqlNameTranslator();");
writer.WriteLine("\t\t\tNpgsqlConnection.GlobalTypeMapper.UseJsonNet(jsonTypes);");
foreach (var table in Tables)
{
if (table.Name == "geometry_columns")
{
writer.WriteLine($"\t\t\tNpgsqlConnection.GlobalTypeMapper.UseLegacyPostgis();");
break;
}
}
if (enums.Count > 0)
{
writer.WriteLine();
foreach (var item in enums)
{
writer.WriteLine($"\t\t\tNpgsqlConnection.GlobalTypeMapper.MapEnum<{item.TypeName}>(\"{item.NspName}.{item.TypeName}\", translator);");
}
}
writer.WriteLine("\t\t}"); // InitializerMapping end
writer.WriteLine();
foreach (var table in Tables)
{
var tableName = MyStagingUtils.ToUpperPascal(table.Name);
writer.WriteLine($"\t\tpublic DbSet<{tableName}> {tableName} {{ get; set; }}");
}
writer.WriteLine("\t}"); // class end
writer.WriteLine("\tpublic partial class NpgsqlNameTranslator : INpgsqlNameTranslator");
writer.WriteLine("\t{");
writer.WriteLine("\t\tpublic string TranslateMemberName(string clrName) => clrName;");
writer.WriteLine("\t\tpublic string TranslateTypeName(string clrTypeName) => clrTypeName;");
writer.WriteLine("\t}");
writer.WriteLine("}"); // namespace end
}
}
