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