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