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