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