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