public void SimpleOneToOneTest()
{
var discoverer = new RelationshipDiscoverer(new HashSet<Type>
{
typeof(Models.SimpleOneToOneTestModel1),
typeof(Models.SimpleOneToOneTestModel2)
});
Assert.Equal(2, discoverer.Discovered.Count);
var relation1 = discoverer.Discovered[0];
Assert.Equal(RelationshipDiscoverer.Relation.RelationType.OtoO, relation1.Type);
Assert.Equal(typeof(Models.SimpleOneToOneTestModel1), relation1.LocalType);
Assert.Equal(typeof(Models.SimpleOneToOneTestModel2), relation1.ForeignType);
Assert.Equal(typeof(Models.SimpleOneToOneTestModel1).GetProperty("Foo"), relation1.LocalProperty);
Assert.Equal(typeof(Models.SimpleOneToOneTestModel2).GetProperty("Bar"), relation1.ForeignProperty);
Assert.Equal("SimpleOneToOneTestModel1s", relation1.LocalTableName);
Assert.Equal("SimpleOneToOneTestModel2s", relation1.ForeignTableName);
Assert.Equal("SimpleOneToOneTestModel1", relation1.LocalTableNameSingular);
Assert.Equal("SimpleOneToOneTestModel2", relation1.ForeignTableNameSingular);
Assert.Equal("SimpleOneToOneTestModel1s", relation1.ForeignKeyTableName);
Assert.Equal("SimpleOneToOneTestModel2Id", relation1.ForeignKeyColumnName);
Assert.Equal(null, relation1.PivotTableName);
Assert.Equal(null, relation1.PivotTableFirstColumnName);
Assert.Equal(null, relation1.PivotTableSecondColumnName);
Assert.Equal(null, relation1.LinkIdentifier);
var relation2 = discoverer.Discovered[1];
Assert.Equal(RelationshipDiscoverer.Relation.RelationType.OtoO, relation2.Type);
Assert.Equal(typeof(Models.SimpleOneToOneTestModel2), relation2.LocalType);
Assert.Equal(typeof(Models.SimpleOneToOneTestModel1), relation2.ForeignType);
Assert.Equal(typeof(Models.SimpleOneToOneTestModel2).GetProperty("Bar"), relation2.LocalProperty);
Assert.Equal(typeof(Models.SimpleOneToOneTestModel1).GetProperty("Foo"), relation2.ForeignProperty);
Assert.Equal("SimpleOneToOneTestModel2s", relation2.LocalTableName);
Assert.Equal("SimpleOneToOneTestModel1s", relation2.ForeignTableName);
Assert.Equal("SimpleOneToOneTestModel2", relation2.LocalTableNameSingular);
Assert.Equal("SimpleOneToOneTestModel1", relation2.ForeignTableNameSingular);
Assert.Equal("SimpleOneToOneTestModel1s", relation2.ForeignKeyTableName);
Assert.Equal("SimpleOneToOneTestModel2Id", relation2.ForeignKeyColumnName);
Assert.Equal(null, relation2.PivotTableName);
Assert.Equal(null, relation2.PivotTableFirstColumnName);
Assert.Equal(null, relation2.PivotTableSecondColumnName);
Assert.Equal(null, relation2.LinkIdentifier);
}
/**
* Runs when a Many:Many relationship is found and it's possible the
* pivot table may already exist. ie: Running migrations on an existing
* database.
*/
protected void UpdatePivotTable(StringBuilder query, RelationshipDiscoverer.Relation relation)
{
// Make sure the table exists, if not we create it.
if (!this.Ctx.Qb.TableExists(relation.PivotTableName))
{
this.CreatePivotTable(relation); return;
}
// Alter or Add Col1
query.Append("ALTER TABLE ");
query.Append(new SqlId(this.Ctx.DatabaseName + ".dbo." + relation.PivotTableName).Value);
var col1Exists = this.Ctx.Qb.ColumnExists(relation.PivotTableName, relation.PivotTableFirstColumnName);
if (col1Exists)
{
query.Append(" ALTER COLUMN ");
}
else
{
query.Append(" ADD ");
}
query.Append(new SqlId(relation.PivotTableFirstColumnName).Value);
query.Append(" INT NOT NULL");
if (!col1Exists && !this.Ctx.Qb.TableEmpty(relation.PivotTableName))
{
query.Append(" DEFAULT ''");
}
query.Append(";\n");
// Alter or Add Col2
query.Append("ALTER TABLE ");
query.Append(new SqlId(this.Ctx.DatabaseName + ".dbo." + relation.PivotTableName).Value);
var col2Exists = this.Ctx.Qb.ColumnExists(relation.PivotTableName, relation.PivotTableSecondColumnName);
if (col2Exists)
{
query.Append(" ALTER COLUMN ");
}
else
{
query.Append(" ADD ");
}
query.Append(new SqlId(relation.PivotTableSecondColumnName).Value);
query.Append(" INT NOT NULL");
if (!col2Exists && !this.Ctx.Qb.TableEmpty(relation.PivotTableName))
{
query.Append(" DEFAULT ''");
}
query.Append(";\n");
// Drop the existing composite primary key.
query.Append("ALTER TABLE ");
query.Append(new SqlId(this.Ctx.DatabaseName + ".dbo." + relation.PivotTableName).Value);
query.Append(" DROP CONSTRAINT ");
query.Append(new SqlId("Pk_" + relation.PivotTableName + "_Id").Value);
query.Append(";\n");
// If data loss is allowed, drop any other columns.
if (DataLossAllowed)
{
using (var reader = this.Ctx.Qb
.SELECT("{0}", new SqlId("COLUMN_NAME"))
.FROM("INFORMATION_SCHEMA.COLUMNS")
.WHERE("{0} = {1}", new SqlId("TABLE_NAME"), relation.PivotTableName)
.Reader
){
while (reader.Read())
{
var existingCol = reader.GetString(0);
if (existingCol != relation.PivotTableFirstColumnName && existingCol != relation.PivotTableSecondColumnName)
{
query.Append("ALTER TABLE ");
query.Append(new SqlId(this.Ctx.DatabaseName + ".dbo." + relation.PivotTableName).Value);
query.Append(" DROP COLUMN ");
query.Append(new SqlId(existingCol).Value);
query.Append(";\n");
}
}
}
}
// Recreate the composite primary key.
query.Append("ALTER TABLE ");
query.Append(new SqlId(this.Ctx.DatabaseName + ".dbo." + relation.PivotTableName).Value);
query.Append(" ADD CONSTRAINT ");
query.Append(new SqlId("Pk_" + relation.PivotTableName + "_Id").Value);
query.Append(" PRIMARY KEY (");
query.Append(new SqlId(relation.PivotTableFirstColumnName).Value);
query.Append(",");
query.Append(new SqlId(relation.PivotTableSecondColumnName).Value);
query.Append(");\n");
}
public void MultipleManyToManyTest()
{
var discoverer = new RelationshipDiscoverer(new HashSet<Type>
{
typeof(Models.MultipleManyToManyTestModel1),
typeof(Models.MultipleManyToManyTestModel2)
});
Assert.Equal(4, discoverer.Discovered.Count);
var relation1 = discoverer.Discovered[0];
Assert.Equal(RelationshipDiscoverer.Relation.RelationType.MtoM, relation1.Type);
Assert.Equal(typeof(Models.MultipleManyToManyTestModel1), relation1.LocalType);
Assert.Equal(typeof(Models.MultipleManyToManyTestModel2), relation1.ForeignType);
Assert.Equal(typeof(Models.MultipleManyToManyTestModel1).GetProperty("FooMultipleManyToManyTestModel2s"), relation1.LocalProperty);
Assert.Equal(typeof(Models.MultipleManyToManyTestModel2).GetProperty("FooMultipleManyToManyTestModel1s"), relation1.ForeignProperty);
Assert.Equal("MultipleManyToManyTestModel1s", relation1.LocalTableName);
Assert.Equal("MultipleManyToManyTestModel2s", relation1.ForeignTableName);
Assert.Equal("MultipleManyToManyTestModel1", relation1.LocalTableNameSingular);
Assert.Equal("MultipleManyToManyTestModel2", relation1.ForeignTableNameSingular);
Assert.Equal(null, relation1.ForeignKeyTableName);
Assert.Equal(null, relation1.ForeignKeyColumnName);
Assert.Equal("MultipleManyToManyTestModel1sFooMultipleManyToManyTestModel2s", relation1.PivotTableName);
Assert.Equal("MultipleManyToManyTestModel1Id", relation1.PivotTableFirstColumnName);
Assert.Equal("MultipleManyToManyTestModel2Id", relation1.PivotTableSecondColumnName);
Assert.Equal("Foo", relation1.LinkIdentifier);
var relation2 = discoverer.Discovered[1];
Assert.Equal(RelationshipDiscoverer.Relation.RelationType.MtoM, relation2.Type);
Assert.Equal(typeof(Models.MultipleManyToManyTestModel1), relation2.LocalType);
Assert.Equal(typeof(Models.MultipleManyToManyTestModel2), relation2.ForeignType);
Assert.Equal(typeof(Models.MultipleManyToManyTestModel1).GetProperty("BarMultipleManyToManyTestModel2s"), relation2.LocalProperty);
Assert.Equal(typeof(Models.MultipleManyToManyTestModel2).GetProperty("BarMultipleManyToManyTestModel1s"), relation2.ForeignProperty);
Assert.Equal("MultipleManyToManyTestModel1s", relation2.LocalTableName);
Assert.Equal("MultipleManyToManyTestModel2s", relation2.ForeignTableName);
Assert.Equal("MultipleManyToManyTestModel1", relation2.LocalTableNameSingular);
Assert.Equal("MultipleManyToManyTestModel2", relation2.ForeignTableNameSingular);
Assert.Equal(null, relation2.ForeignKeyTableName);
Assert.Equal(null, relation2.ForeignKeyColumnName);
Assert.Equal("MultipleManyToManyTestModel1sBarMultipleManyToManyTestModel2s", relation2.PivotTableName);
Assert.Equal("MultipleManyToManyTestModel1Id", relation2.PivotTableFirstColumnName);
Assert.Equal("MultipleManyToManyTestModel2Id", relation2.PivotTableSecondColumnName);
Assert.Equal("Bar", relation2.LinkIdentifier);
var relation3 = discoverer.Discovered[2];
Assert.Equal(RelationshipDiscoverer.Relation.RelationType.MtoM, relation3.Type);
Assert.Equal(typeof(Models.MultipleManyToManyTestModel2), relation3.LocalType);
Assert.Equal(typeof(Models.MultipleManyToManyTestModel1), relation3.ForeignType);
Assert.Equal(typeof(Models.MultipleManyToManyTestModel2).GetProperty("FooMultipleManyToManyTestModel1s"), relation3.LocalProperty);
Assert.Equal(typeof(Models.MultipleManyToManyTestModel1).GetProperty("FooMultipleManyToManyTestModel2s"), relation3.ForeignProperty);
Assert.Equal("MultipleManyToManyTestModel2s", relation3.LocalTableName);
Assert.Equal("MultipleManyToManyTestModel1s", relation3.ForeignTableName);
Assert.Equal("MultipleManyToManyTestModel2", relation3.LocalTableNameSingular);
Assert.Equal("MultipleManyToManyTestModel1", relation3.ForeignTableNameSingular);
Assert.Equal(null, relation3.ForeignKeyTableName);
Assert.Equal(null, relation3.ForeignKeyColumnName);
Assert.Equal("MultipleManyToManyTestModel1sFooMultipleManyToManyTestModel2s", relation3.PivotTableName);
Assert.Equal("MultipleManyToManyTestModel1Id", relation3.PivotTableFirstColumnName);
Assert.Equal("MultipleManyToManyTestModel2Id", relation3.PivotTableSecondColumnName);
Assert.Equal("Foo", relation3.LinkIdentifier);
var relation4 = discoverer.Discovered[3];
Assert.Equal(RelationshipDiscoverer.Relation.RelationType.MtoM, relation4.Type);
Assert.Equal(typeof(Models.MultipleManyToManyTestModel2), relation4.LocalType);
Assert.Equal(typeof(Models.MultipleManyToManyTestModel1), relation4.ForeignType);
Assert.Equal(typeof(Models.MultipleManyToManyTestModel2).GetProperty("BarMultipleManyToManyTestModel1s"), relation4.LocalProperty);
Assert.Equal(typeof(Models.MultipleManyToManyTestModel1).GetProperty("BarMultipleManyToManyTestModel2s"), relation4.ForeignProperty);
Assert.Equal("MultipleManyToManyTestModel2s", relation4.LocalTableName);
Assert.Equal("MultipleManyToManyTestModel1s", relation4.ForeignTableName);
Assert.Equal("MultipleManyToManyTestModel2", relation4.LocalTableNameSingular);
Assert.Equal("MultipleManyToManyTestModel1", relation4.ForeignTableNameSingular);
Assert.Equal(null, relation4.ForeignKeyTableName);
Assert.Equal(null, relation4.ForeignKeyColumnName);
Assert.Equal("MultipleManyToManyTestModel1sBarMultipleManyToManyTestModel2s", relation4.PivotTableName);
Assert.Equal("MultipleManyToManyTestModel1Id", relation4.PivotTableFirstColumnName);
Assert.Equal("MultipleManyToManyTestModel2Id", relation4.PivotTableSecondColumnName);
Assert.Equal("Bar", relation4.LinkIdentifier);
}
protected void UpdateFKToLTOo(StringBuilder query, RelationshipDiscoverer.Relation relation)
{
// Add the column to current table we are about to create.
query.Append("ALTER TABLE ");
query.Append(new SqlId(this.Ctx.DatabaseName + ".dbo." + relation.ForeignKeyTableName).Value);
var colExists = this.Ctx.Qb.ColumnExists(relation.ForeignKeyTableName, relation.ForeignKeyColumnName);
if (colExists)
{
query.Append(" ALTER COLUMN ");
}
else
{
query.Append(" ADD ");
}
query.Append(new SqlId(relation.ForeignKeyColumnName).Value);
query.Append(" INT NULL");
query.Append(";\n");
// Ensure the foreign key contraint gets added.
FkConstraints.Add(new FkConstraint
{
LocalTableName = relation.ForeignKeyTableName,
LocalColumnName = relation.ForeignKeyColumnName,
ForeignTableName = relation.ForeignTableName,
ForeignColumnName = "Id"
});
}
/**
* Because the creation of the foreign key is defered,
* the implemenation at this point is exactly the same.
*/
protected void UpdateFKToFT(RelationshipDiscoverer.Relation relation)
{
this.AddFKToFT(relation);
}
/**
* When a Many to Many relationship is found, this will first check to
* see if the Pivot Table already exists and if not we then create the
* needed Pivot Table to support the relationship.
*/
protected void CreatePivotTable(RelationshipDiscoverer.Relation relation)
{
// It is possible the table has already been created.
if (this.Ctx.Qb.TableExists(relation.PivotTableName)) return;
// Okay lets create it.
this.Ctx.Qb.Execute
(
"CREATE TABLE @TableName\n" +
"(\n" +
"\t@Col1 INT NOT NULL,\n" +
"\t@Col2 INT NOT NULL,\n" +
"\tCONSTRAINT @Contraint\n" +
"\tPRIMARY KEY (@Col1,@Col2)\n" +
");",
new Dictionary<string, object>
{
{"@TableName", new SqlId(this.Ctx.DatabaseName + ".dbo." + relation.PivotTableName)},
{"@Col1", new SqlId(relation.PivotTableFirstColumnName)},
{"@Col2", new SqlId(relation.PivotTableSecondColumnName)},
{"@Contraint", new SqlId("Pk_"+relation.PivotTableName+"_Id")}
}
);
// Make sure the foreign key contraints get created.
FkConstraints.Add(new FkConstraint
{
LocalTableName = relation.PivotTableName,
LocalColumnName = relation.PivotTableFirstColumnName,
ForeignTableName = relation.LocalTableName,
ForeignColumnName = "Id"
});
FkConstraints.Add(new FkConstraint
{
LocalTableName = relation.PivotTableName,
LocalColumnName = relation.PivotTableSecondColumnName,
ForeignTableName = relation.ForeignTableName,
ForeignColumnName = "Id"
});
}
/**
* Adds a new foreign key to the local table, that supports a 1:1.
*/
protected void AddFKToLTOo(StringBuilder query, RelationshipDiscoverer.Relation relation)
{
// Add the column to current table we are about to create.
query.Append("\t");
query.Append(new SqlId(relation.ForeignKeyColumnName).Value);
query.Append(" INT NULL,\n");
// Ensure the foreign key contraint gets added.
FkConstraints.Add(new FkConstraint
{
LocalTableName = relation.ForeignKeyTableName,
LocalColumnName = relation.ForeignKeyColumnName,
ForeignTableName = relation.ForeignTableName,
ForeignColumnName = "Id"
});
}
/**
* Adds a new foreign key to the foreign table, that supports a Many:1.
*
* > NOTE: This will be deferred until it comes time
* > to actually create the foreign table.
*/
protected void AddFKToFT(RelationshipDiscoverer.Relation relation)
{
// Create a new ForeignKey, the creation is defered.
ForeignKeys.Add(new ForeignKey
{
TableName = relation.ForeignKeyTableName,
ColumnName = relation.ForeignKeyColumnName
});
// Ensure the foreign key contraint gets added also.
FkConstraints.Add(new FkConstraint
{
LocalTableName = relation.ForeignKeyTableName,
LocalColumnName = relation.ForeignKeyColumnName,
ForeignTableName = relation.LocalTableName,
ForeignColumnName = "Id"
});
}
