private static async Task RecreateSchemaAsync(MdbContext mdb, SQLStatementWriter sb, JMXSchema schema, JMXSchema fromDbSchema)
{
int recCount = await mdb.ExecuteAsync <int>($"select count(*) from {fromDbSchema.DbObjectName}");
foreach (var fk in fromDbSchema.ParentRelations)
{
sb.WriteDropParentRelationStatement(fk);
}
foreach (var fk in fromDbSchema.ForeignKeys)
{
sb.WriteDropFKStatement(fk, fromDbSchema);
}
string tmpTableName = fromDbSchema.DbObjectName.ObjectName + "_" + DateTime.Now.Subtract(vbo.Date()).Seconds.ToString();
if (recCount > 0)
{
sb.WriteRenameTableStatement(tmpTableName, fromDbSchema);
}
else
{
sb.WriteDropTableStatement(null, fromDbSchema);
}
sb.WriteCreateTableStatements(schema);
if (recCount > 0)
{
sb.WriteInsertRowsStatement(tmpTableName, schema);
sb.WriteDropTableStatement(tmpTableName, fromDbSchema);
}
sb.WriteCreatePKStatement(schema);
foreach (var att in schema.Attributes)
{
sb.WriteCreateConstraintStatement(att, schema);
}
foreach (var index in schema.Indexes)
{
sb.WriteCreateIndexStatement(index, schema);
}
foreach (var fk in schema.ForeignKeys)
{
sb.WriteCreateFKStatement(fk, schema);
}
/// Need test
foreach (var fk in fromDbSchema.ParentRelations)
{
sb.WriteCreateParentRelationStatement(fk);
}
}
private static void CompareFK(SQLStatementWriter sb, JMXSchema schema, JMXSchema fromDbSchema)
{
List <(JMXForeignKey i1, JMXForeignKey i2, DbObjectOnDiffActions action)> l =
new List <(JMXForeignKey i1, JMXForeignKey i2, DbObjectOnDiffActions action)>();
int count = schema.ForeignKeys.Count;
for (int i = 0; i < count; i++)
{
var k1 = schema.ForeignKeys[i];
var k2 = fromDbSchema.ForeignKeys.FirstOrDefault(fk => fk.KeyName == k1.KeyName);
if (k2 == null)
{
l.Add((k1, k2, DbObjectOnDiffActions.add));
}
else if (k1.CheckOption != k2.CheckOption ||
k1.DeleteRefAction != k2.DeleteRefAction ||
k1.UpdateRefAction != k2.UpdateRefAction ||
!k1.RefDbObjectName.ToString().Equals(k2.RefDbObjectName.ToString(), StringComparison.OrdinalIgnoreCase))
{
l.Add((k1, k2, DbObjectOnDiffActions.alter));
}
else
{
foreach (var m in k1.KeyMembers)
{
var m2 = k2.KeyMembers.FirstOrDefault(memeber => memeber.FieldName == m.FieldName && memeber.Position == m.Position);
if (m != m2)
{
l.Add((k1, k2, DbObjectOnDiffActions.alter));
break;
}
}
foreach (var m in k1.RefKeyMembers)
{
var m2 = k2.RefKeyMembers.FirstOrDefault(memeber => memeber.FieldName == m.FieldName && memeber.Position == m.Position);
if (m != m2)
{
l.Add((k1, k2, DbObjectOnDiffActions.alter));
break;
}
}
}
}
count = fromDbSchema.ForeignKeys.Count;
for (int i = 0; i < count; i++)
{
var k2 = fromDbSchema.ForeignKeys[i];
var k1 = schema.ForeignKeys.FirstOrDefault(fk => fk.KeyName == k2.KeyName);
if (k1 == null)
{
l.Add((k1, k2, DbObjectOnDiffActions.drop));
}
}
foreach (var(i1, i2, action) in l)
{
if (action == DbObjectOnDiffActions.drop)
{
sb.WriteDropFKStatement(i2, fromDbSchema);
}
}
foreach (var(i1, i2, action) in l)
{
if (action == DbObjectOnDiffActions.add)
{
sb.WriteCreateFKStatement(i1);
}
else if (action == DbObjectOnDiffActions.alter)
{
sb.WriteDropFKStatement(i2, fromDbSchema);
sb.WriteCreateFKStatement(i1);
}
}
}
