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 CompareIndexes(SQLStatementWriter sb, JMXSchema schema, JMXSchema fromDbSchema)
{
List <(JMXIndex i1, JMXIndex i2, DbObjectOnDiffActions action)> l =
new List <(JMXIndex i1, JMXIndex i2, DbObjectOnDiffActions action)>();
int count = schema.Indexes.Count;
for (int i = 0; i < count; i++)
{
var i1 = schema.Indexes[i];
var i2 = fromDbSchema.Indexes.FirstOrDefault(index => index.IndexName == i1.IndexName);
if (i2 == null)
{
l.Add((i1, i2, DbObjectOnDiffActions.add));
}
else if (i1.ClusteredOption != i2.ClusteredOption)
{
l.Add((i1, i2, DbObjectOnDiffActions.alter));
}
else if (i1.IsUnique != i2.IsUnique)
{
l.Add((i1, i2, DbObjectOnDiffActions.alter));
}
else
{
foreach (var m in i1.KeyMembers)
{
var m2 = i2.KeyMembers.FirstOrDefault(memeber => memeber.FieldName == m.FieldName && memeber.Position == m.Position);
if (m != m2)
{
l.Add((i1, i2, DbObjectOnDiffActions.alter));
break;
}
}
}
}
count = fromDbSchema.Indexes.Count;
for (int i = 0; i < count; i++)
{
var i2 = fromDbSchema.Indexes[i];
var i1 = schema.Indexes.FirstOrDefault(index => index.IndexName == i2.IndexName);
if (i1 == null)
{
l.Add((i1, i2, DbObjectOnDiffActions.drop));
}
}
foreach (var(i1, i2, action) in l)
{
if (action == DbObjectOnDiffActions.drop)
{
sb.WriteDropIndexStatement(i2, fromDbSchema);
}
}
foreach (var(i1, i2, action) in l)
{
if (action == DbObjectOnDiffActions.add)
{
sb.WriteCreateIndexStatement(i1);
}
else if (action == DbObjectOnDiffActions.alter)
{
sb.WriteDropIndexStatement(i2, fromDbSchema);
sb.WriteCreateIndexStatement(i1);
}
}
}
