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