protected void UpdateFKToLTOm(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"
});
}
/**
* 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"
});
}
/**
* 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"
});
}
/**
* 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);
}
/**
* 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");
}