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 } }