public void Only_nulls_out_minimum_necessary_regardless_of_order_added()
{
// Arrange
var sut = new SchemaRelations();
sut.AddTable("A");
sut.AddTable("B");
sut.AddTable("C");
sut.AddTable("D");
sut.AddRelationship("B", "A", "A -> B", false);
sut.AddRelationship("C", "B", "B ~> C", true);
sut.AddRelationship("A", "B", "B ~> A", true);
sut.AddRelationship("D", "C", "C ~> D", true);
// Assert
var result = sut.GetClearDataCommands();
// Assert
result.ShouldAllBeEquivalentTo(new object[]
{
new NullColumnCommand("B", "B ~> A"),
new ClearTableCommand("A"),
new ClearTableCommand("B"),
new ClearTableCommand("C"),
new ClearTableCommand("D")
},
o => o.RespectingRuntimeTypes().WithStrictOrdering());
}
public void Works()
{
// Arrange
var sut = new SchemaRelations(new[] { "public.blob_container", "public.tenant", "public.test", "public.blob", "public.test_run" });
sut.AddRelationship("public.blob_container", "public.blob", "container_id", false);
sut.AddRelationship("public.tenant", "public.blob_container", "tenant_id", false);
sut.AddRelationship("public.test", "public.test_run", "test_id", false);
sut.AddRelationship("public.blob", "public.test_run", "artifact", true);
sut.AddRelationship("public.blob", "public.test_run", "artifact_diff", true);
// Assert
var result = sut.GetClearDataCommands();
// Assert
result.Should().BeEquivalentTo(new object[]
{
new ClearTableCommand("public.test_run"),
new ClearTableCommand("public.test"),
new ClearTableCommand("public.blob"),
new ClearTableCommand("public.blob_container"),
new ClearTableCommand("public.tenant")
},
o => o.RespectingRuntimeTypes().WithStrictOrdering());
}
public void Nulls_multiple_foreign_keys()
{
// Arrange
var sut = new SchemaRelations();
sut.AddTable("A");
sut.AddTable("B");
sut.AddTable("C");
sut.AddRelationship("A", "B", "B -> A", false);
sut.AddRelationship("C", "A", "A -> C", false);
sut.AddRelationship("B", "A", "A ~> B", true);
sut.AddRelationship("B", "C", "C ~> B", true);
// Assert
var result = sut.GetClearDataCommands();
// Assert
result.ShouldAllBeEquivalentTo(new object[]
{
new NullColumnCommand("A", "A ~> B"),
new NullColumnCommand("C", "C ~> B"),
new ClearTableCommand("B"),
new ClearTableCommand("A"),
new ClearTableCommand("C")
},
o => o.RespectingRuntimeTypes().WithStrictOrdering());
}
public void Clears_leaf_tables_before_nulling_foreign_keys_at_any_point_in_chain()
{
// Arrange
var sut = new SchemaRelations();
sut.AddTable("A");
sut.AddTable("B");
sut.AddTable("C");
sut.AddRelationship("A", "B", "B -> A", false);
sut.AddRelationship("B", "A", "A ~> B", true);
sut.AddRelationship("B", "C", "C -> B", false);
// Act
var result = sut.GetClearDataCommands();
// Assert
result.ShouldAllBeEquivalentTo(new object[]
{
new ClearTableCommand("C"),
new NullColumnCommand("A", "A ~> B"),
new ClearTableCommand("B"),
new ClearTableCommand("A")
},
o => o.RespectingRuntimeTypes().WithStrictOrdering());
}
public void Returns_no_commands_when_there_are_no_tables()
{
// Arrange
var sut = new SchemaRelations(Enumerable.Empty <string>());
// Act
var result = sut.GetClearDataCommands();
// Assert
result.Should().BeEmpty();
}
public void ClearAllData(IDapperConnection connection, int?commandTimeout = null)
{
if (!(connection.Dialect is ISchemaQueryDialect dialect))
{
throw new ArgumentException($"The dialect '{connection.Dialect.Name}' does not support querying the schema and can therefore not be used");
}
var ignoredTables = new HashSet <string>(this.IgnoredTables ?? Enumerable.Empty <string>());
var tables = connection.Query <AllTablesQueryResult>(dialect.MakeGetAllTablesStatement());
var relations = connection.Query <TableRelationsQueryResult>(dialect.MakeGetAllRelationsStatement());
var schemaRelations = new SchemaRelations();
foreach (var table in tables)
{
if (!ignoredTables.Contains(table.Name))
{
schemaRelations.AddTable(table.Name);
}
}
foreach (var relation in relations)
{
schemaRelations.AddRelationship(relation.ReferencedTable, relation.ReferencingTable, relation.ReferencingColumn, relation.RelationIsOptional);
}
var commands = schemaRelations.GetClearDataCommands();
foreach (var command in commands)
{
switch (command)
{
case ClearTableCommand c:
{
var tableSchema = new TableSchema(c.TableName, ImmutableArray <ColumnSchema> .Empty);
var sql = dialect.MakeDeleteRangeStatement(tableSchema, null);
connection.Execute(sql, commandTimeout: commandTimeout);
break;
}
case NullColumnCommand c:
{
var sql = dialect.MakeSetColumnNullStatement(c.TableName, c.ColumnName);
connection.Execute(sql, commandTimeout: commandTimeout);
break;
}
default:
throw new InvalidOperationException("Unknown sql command: " + command?.GetType());
}
}
}
public void Throws_exception_if_there_are_no_nullable_foreign_keys()
{
// Arrange
var sut = new SchemaRelations(new[] { "A", "B", "C" });
sut.AddRelationship("A", "B", "B -> A", false);
sut.AddRelationship("B", "A", "A -> B", false);
// Act
Action act = () => sut.GetClearDataCommands();
// Assert
act.Should().Throw <InvalidOperationException>();
}
public static List <SqlCommand> GenerateWipeDatabaseSql(ISqlConnection connection, IEnumerable <string> ignoredTables = null)
{
if (!(connection.Config.Dialect is ISchemaQueryDialect dialect))
{
throw new ArgumentException($"The dialect '{connection.Config.Dialect.GetType().Name}' does not support querying the schema");
}
var allTablesStatement = dialect.MakeGetAllTablesStatement();
var tables = connection.Query <AllTablesQueryResult>(allTablesStatement.CommandText, allTablesStatement.Parameters)
.Select(t => t.Name)
.Except(ignoredTables ?? Enumerable.Empty <string>())
.OrderBy(t => t)
.ToList();
var allRelationsStatement = dialect.MakeGetAllRelationsStatement();
var relations = connection.Query <TableRelationsQueryResult>(allRelationsStatement.CommandText, allRelationsStatement.Parameters);
var schemaRelations = new SchemaRelations(tables);
foreach (var relation in relations)
{
schemaRelations.AddRelationship(relation.TargetTable, relation.SourceTable, relation.SourceColumn, relation.SourceColumnIsOptional);
}
var commands = new List <SqlCommand>();
foreach (var command in schemaRelations.GetClearDataCommands())
{
switch (command)
{
case ClearTableCommand c:
{
commands.Add(dialect.MakeDeleteAllCommand(c.TableName));
break;
}
case NullColumnCommand c:
{
commands.Add(dialect.MakeSetColumnNullStatement(c.TableName, c.ColumnName));
break;
}
default:
throw new InvalidOperationException("Unknown sql command: " + command?.GetType());
}
}
return(commands);
}
public void Returns_clear_table_commands_when_there_are_no_tables_with_relations()
{
// Arrange
var sut = new SchemaRelations(new[] { "Foo", "Bar" });
// Act
var result = sut.GetClearDataCommands();
// Assert
result.Should().BeEquivalentTo(new[]
{
new ClearTableCommand("Foo"),
new ClearTableCommand("Bar")
},
o => o.RespectingRuntimeTypes());
}
public void Ignores_relationships_for_tables_which_dont_exist()
{
// Arrange
var sut = new SchemaRelations(new[] { "A" });
sut.AddRelationship("B", "A", "A -> B", false);
// Assert
var result = sut.GetClearDataCommands();
// Assert
result.Should().BeEquivalentTo(new[]
{
new ClearTableCommand("A")
},
o => o.RespectingRuntimeTypes());
}
public void Returns_clear_table_command_for_referencing_table_before_referenced_table()
{
// Arrange
var sut = new SchemaRelations(new[] { "Foo", "Bar" });
sut.AddRelationship("Foo", "Bar", "Foo_Bar", false);
// Act
var result = sut.GetClearDataCommands();
// Assert
result.Should().BeEquivalentTo(new[]
{
new ClearTableCommand("Bar"),
new ClearTableCommand("Foo")
},
o => o.RespectingRuntimeTypes().WithStrictOrdering());
}
public void Clears_data_from_table_with_two_foreign_keys()
{
// Arrange
var sut = new SchemaRelations(new[] { "A", "B" });
sut.AddRelationship("B", "A", "A 1-> B", false);
sut.AddRelationship("B", "A", "A 2-> B", false);
// Assert
var result = sut.GetClearDataCommands();
// Assert
result.Should().BeEquivalentTo(new object[]
{
new ClearTableCommand("A"),
new ClearTableCommand("B")
},
o => o.RespectingRuntimeTypes().WithStrictOrdering());
}
public void Clears_foreign_key_columns_before_clearing_recursive_relations()
{
// Arrange
var sut = new SchemaRelations(new[] { "A", "B" });
sut.AddRelationship("A", "B", "B -> A", false);
sut.AddRelationship("B", "A", "A ~> B", true);
// Act
var result = sut.GetClearDataCommands();
// Assert
result.Should().BeEquivalentTo(new object[]
{
new NullColumnCommand("A", "A ~> B"),
new ClearTableCommand("B"),
new ClearTableCommand("A")
},
o => o.RespectingRuntimeTypes().WithStrictOrdering());
}