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 bool CompareAttributes(SQLStatementWriter sb, JMXSchema schema, JMXSchema fromDbSchema)
{
bool recreate = false;
int count = schema.Attributes.Count;
List <(JMXAttribute att, JMXAttribute att2, AttribCompareDiff diff)> l =
new List <(JMXAttribute, JMXAttribute, AttribCompareDiff)>();
for (int i = 0; i < count; i++)
{
var att = schema.Attributes[i];
var att2 = fromDbSchema.Attributes.FirstOrDefault(a =>
a.FieldName.Equals(att.FieldName, StringComparison.OrdinalIgnoreCase));
bool found = (att2 != null);
AttribCompareDiff diff = AttribCompareDiff.none;
if (found)
{
if (!att.ServerDataType.Equals(att2.ServerDataType, StringComparison.OrdinalIgnoreCase))
{
diff = AttribCompareDiff.dataTtype;
}
if (att.Required != att2.Required)
{
diff |= AttribCompareDiff.nullOptions;
}
if (att.Identity.IsIdentity != att2.Identity.IsIdentity ||
att.Identity.Seed != att2.Identity.Seed ||
att.Identity.Increment != att2.Identity.Increment)
{
diff |= AttribCompareDiff.identity;
}
if (att.DataType != MdbType.@object &&
!att.AttribName.Equals(att2.FieldName, StringComparison.OrdinalIgnoreCase))
{
diff |= AttribCompareDiff.name;
}
//Server DataTypes is equals
if ((diff & AttribCompareDiff.dataTtype) != AttribCompareDiff.dataTtype)
{
MdbTypeInfo ti = SqlServerHelper.TypeInfo[att.ServerDataType];
if (!ti.FixedSize)
{
if (SqlServerHelper.TypeMapping.GetVariableLenghtDataTypes().Contains(att.ServerDataType) && att.DataSize.Size != att2.DataSize.Size)
{
diff |= AttribCompareDiff.size;
}
else if (ti.MdbType == MdbType.@decimal && (att.DataSize.Precision != att2.DataSize.Precision ||
att.DataSize.Scale != att2.DataSize.Scale))
{
diff |= AttribCompareDiff.size;
}
}
}
if (!att.CheckConstraint
.Definition
.RemoveChar("[( )]".ToCharArray())
.Equals(att2.CheckConstraint
.Definition
.RemoveChar("[( )]".ToCharArray()),
StringComparison.OrdinalIgnoreCase))
{
diff |= AttribCompareDiff.constraint;
}
else if (!att.DefaultConstraint
.Definition.RemoveChar("[( )]".ToCharArray())
.Equals(att2.DefaultConstraint
.Definition
.RemoveChar("[( )]".ToCharArray()),
StringComparison.OrdinalIgnoreCase))
{
diff |= AttribCompareDiff.constraint;
}
}
//else if (att2 != null)
// diff |= AttribCompareDiff.name;
else
{
diff = AttribCompareDiff.notFound;
}
l.Add((att, att2, diff));
}
foreach (var att2 in fromDbSchema.Attributes)
{
//var att = schema.Attributes.FirstOrDefault(a => a.ID == att2.ID);
var att = schema.Attributes.FirstOrDefault(a =>
a.FieldName.Equals(att2.FieldName, StringComparison.OrdinalIgnoreCase));
if (att == null)
{
l.Add((att, att2, AttribCompareDiff.remove));
}
}
foreach (var(att, att2, diff) in l)
{
if ((diff & AttribCompareDiff.remove) == AttribCompareDiff.remove)
{
sb.WriteDropColumnStatement(att2);
continue;
}
if ((diff & AttribCompareDiff.constraint) == AttribCompareDiff.constraint)
{
sb.WriteDropConstraintStatement(att2, fromDbSchema);
sb.WriteCreateConstraintStatement(att);
}
if ((diff & AttribCompareDiff.dataTtype) == AttribCompareDiff.dataTtype ||
(diff & AttribCompareDiff.size) == AttribCompareDiff.size ||
(diff & AttribCompareDiff.nullOptions) == AttribCompareDiff.nullOptions)
{
sb.WriteAlterColumnStatement(att);
}
else if ((diff & AttribCompareDiff.notFound) == AttribCompareDiff.notFound)
{
sb.WriteAlterColumnStatement(att, true);
}
else if ((diff & AttribCompareDiff.name) == AttribCompareDiff.name)
{
att.FieldName = att.AttribName;
sb.WriteRenameColumnStatement(att2.FieldName, att.FieldName);
}
else if ((diff & AttribCompareDiff.identity) == AttribCompareDiff.identity)
{
recreate = true;
}
}
return(recreate);
}