private static bool ComparePK(SQLStatementWriter sb, JMXSchema schema, JMXSchema fromDbSchema)
{
///Add PK compare
///writeDropPKStatement
///writeCreatePKStatement
///The constraint 'XPK1Requests' is being referenced by table 'PaymentStateHists',
///foreign key constraint 'FK_PAYMENTSTATEHISTS_REQUESTS'.
///Could not drop constraint. See previous errors.
if (schema.PrimaryKey == null && fromDbSchema.PrimaryKey != null)
{
//writeDropPKStatement(sb, fromDbSchema);
return(true);
}
else if (schema.PrimaryKey != null && fromDbSchema.PrimaryKey == null)
{
sb.WriteCreatePKStatement();
}
else if (schema.PrimaryKey != null &&
schema.PrimaryKey.KeyName != fromDbSchema.PrimaryKey.KeyName ||
schema.PrimaryKey.KeyMembers == fromDbSchema.PrimaryKey.KeyMembers)
{
//writeDropPKStatement(sb, fromDbSchema);
//writeCreatePKStatement(sb, schema);
return(true);
}
return(false);
}
public async Task <DataPackage> InvokeAsync(ActionInfo ai, DataPackage dp)
{
GetParameters(ai, dp);
var ctx = ai.GetContext();
JMXFactory f = ctx.CreateJMXFactory(_connectionName);
IJMXRepo repo = f.SchemaFactory.CreateJMXRepo();
using (SQLStatementWriter writer = f.CreateSQLStatementWriter())
{
JMXSchema schema = await repo.GetSchemaAsync(_viewName);
string body = writer.WriteSelectStatement(
schema,
_conditions.ToArray())
.ToString();
if (schema.DbObjectType == DbObjectTypes.Action)
{
ActionManager am = ctx.Services.GetRequiredService <ActionManager>();
return(await am.ExecuteAsync(am.GetActionInfo(body), dp));
}
else
{
return(await f
.GetMdbContext()
.GetReaderAsync(body, CreateParameters(schema)));
}
}
}
private static string[] CreateNewStatements(JMXSchema schema)
{
List <string> sql = new List <string>();
using (SQLStatementWriter sb = new SQLStatementWriter(SqlServerHelper.TypeMapping, schema))
{
sb.WriteCreateNewTableStatements();
sql.Add(sb.ToString());
}
return(sql.ToArray());
}
private async Task <string[]> DropSchemaStatementsAsync(JMXSchema fromDbSchema)
{
List <string> sql = new List <string>();
using (SQLStatementWriter sb = new SQLStatementWriter(SqlServerHelper.TypeMapping, fromDbSchema))
{
await WriteDropStatementsAsync(sb, fromDbSchema);
sql.Add(sb.ToString());
}
return(sql.ToArray());
}
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 async Task WriteDropStatementsAsync(SQLStatementWriter sb, JMXSchema fromDbSchema)
{
foreach (var att in fromDbSchema.Attributes.Where(a => a.FieldName.StartsWith(JMXFactory.DETAIL_FIELD_PREFIX)))
{
string[] names = att.FieldName.Split('_');
var schemaFromDb = await GetObjectSchemaAsync(new JMXObjectName(names[1], names[2]).ToString());
if (schemaFromDb != null)
{
await WriteDropStatementsAsync(sb, schemaFromDb);
}
}
sb.WriteDropStatements(fromDbSchema);
}
/// <summary>
/// !!! Add to GetTableSchema detail references as attributes for cascade delete FK
/// </summary>
/// <param name="sb"></param>
/// <param name="schema"></param>
/// <param name="fromDbSchema"></param>
/// <returns></returns>
private async Task CompareSchemasStatementsAsync(SQLStatementWriter sb, JMXSchema schema, JMXSchema fromDbSchema)
{
MdbContext mdb = Factory.GetMdbContext();
bool recreate = false;
foreach (var att in fromDbSchema.Attributes.Where(a => a.FieldName.StartsWith(JMXFactory.DETAIL_FIELD_PREFIX)))
{
if (!schema.Attributes.Any(a => a.FieldName == att.FieldName))
{
string[] names = att.FieldName.Split('_');
var schemaFromDb = await GetObjectSchemaAsync(new JMXObjectName(names[1], names[2]).ToString());
if (schemaFromDb != null)
{
await WriteDropStatementsAsync(sb, schemaFromDb);
}
}
}
recreate = CompareAttributes(sb, schema, fromDbSchema);
if (!recreate)
{
recreate = ComparePK(sb, schema, fromDbSchema);
}
if (!recreate)
{
CompareIndexes(sb, schema, fromDbSchema);
CompareFK(sb, schema, fromDbSchema);
}
else
{
await RecreateSchemaAsync(mdb, sb, schema, fromDbSchema);
}
foreach (var att in schema.Attributes.Where(a => a.DataType == MdbType.@object))
{
var schemaFromDb = await GetObjectSchemaAsync(att.ObjectSchema.DbObjectName.ToString());
if (schemaFromDb != null)
{
await CompareSchemasStatementsAsync(sb, att.ObjectSchema, schemaFromDb);
}
else
{
sb.WriteCreateNewTableStatements(att.ObjectSchema);
}
}
}
private async Task <string[]> CompareSchemasAsync(JMXSchema schema, JMXSchema fromDbSchema)
{
List <string> sql = new List <string>();
using (SQLStatementWriter sb = new SQLStatementWriter(SqlServerHelper.TypeMapping, schema))
{
await CompareSchemasStatementsAsync(sb, schema, fromDbSchema);
string stmt = sb.ToString();
if (stmt.Length > 1)
{
sql.Add(sb.ToString());
}
}
return(sql.ToArray());
}
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);
}
}
}
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);
}
}
}
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);
}
