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