/// <summary>
/// Helper function for generating the scripts for tables & constraints.
/// </summary>
/// <param name="itemCollection"> </param>
/// <returns> </returns>
internal static List<string> CreateObjectsScript(StoreItemCollection itemCollection, bool returnWarnings)
{
var builder = new SqlDdlBuilder();
// Iterate over the container.
foreach (var container in itemCollection.GetItems<EntityContainer>())
{
// Generate create table statements.
foreach (var set in container.BaseEntitySets)
{
// If it is a type of entitySet, generate Create Table statements.
var entitySet = set as EntitySet;
if (entitySet != null)
{
builder.AppendCreateTable(entitySet);
}
}
// Generate Foreign Key constraints.
foreach (var set in container.BaseEntitySets)
{
// If it is association set, generate Foreign Key constraints.
var associationSet = set as AssociationSet;
if (associationSet != null)
{
builder.AppendCreateForeignKeys(associationSet);
}
}
}
// Return the final command text.
return builder.GetCommandText(returnWarnings);
}
static IEnumerable<string> GenerateForeignKeys(StoreItemCollection storeItems)
{
foreach (var associationSet in storeItems.GetItems<EntityContainer>()[0].BaseEntitySets.OfType<AssociationSet>())
{
var result = new StringBuilder();
ReferentialConstraint constraint = associationSet.ElementType.ReferentialConstraints.Single<ReferentialConstraint>();
AssociationSetEnd end = associationSet.AssociationSetEnds[constraint.FromRole.Name];
AssociationSetEnd end2 = associationSet.AssociationSetEnds[constraint.ToRole.Name];
result.AppendFormat("ALTER TABLE {0}.{1} ADD FOREIGN KEY ({2}) REFERENCES {3}.{4}({5}){6};",
SqlGenerator.QuoteIdentifier(MetadataHelpers.GetSchemaName(end2.EntitySet)),
SqlGenerator.QuoteIdentifier(MetadataHelpers.GetTableName(end2.EntitySet)),
string.Join(", ", constraint.ToProperties.Select(fk => SqlGenerator.QuoteIdentifier(fk.Name))),
SqlGenerator.QuoteIdentifier(MetadataHelpers.GetSchemaName(end.EntitySet)),
SqlGenerator.QuoteIdentifier(MetadataHelpers.GetTableName(end.EntitySet)),
string.Join(", ", constraint.FromProperties.Select(pk => SqlGenerator.QuoteIdentifier(pk.Name))),
end.CorrespondingAssociationEndMember.DeleteBehavior == OperationAction.Cascade ? " ON DELETE CASCADE" : string.Empty);
yield return result.ToString();
}
}
public static string DropObjects(StoreItemCollection itemCollection)
{
var builder = new SqlScripts();
foreach (var container in itemCollection.GetItems<EntityContainer>())
{
var entitySets = container.BaseEntitySets.OfType<EntitySet>().OrderBy(s => s.Name);
foreach (var associationSet in container.BaseEntitySets.OfType<AssociationSet>().OrderBy(s => s.Name))
{
builder.AppendDropForeignKeys(associationSet);
}
foreach (var entitySet in container.BaseEntitySets.OfType<EntitySet>().OrderBy(s => s.Name))
{
builder.AppendDropTable(entitySet);
}
}
return builder.GetCommandText();
}
static IEnumerable<string> GenerateTables(StoreItemCollection storeItems)
{
foreach (var entitySet in storeItems.GetItems<EntityContainer>()[0].BaseEntitySets.OfType<EntitySet>())
{
var result = new StringBuilder();
var tableName = MetadataHelpers.GetTableName(entitySet);
var schemaName = MetadataHelpers.GetSchemaName(entitySet);
result.AppendFormat("CREATE TABLE {0}.{1} (", SqlGenerator.QuoteIdentifier(schemaName), SqlGenerator.QuoteIdentifier(tableName));
result.AppendLine();
result.Append("\t");
result.Append(string.Join("," + Environment.NewLine + "\t", MetadataHelpers.GetProperties(entitySet.ElementType).Select(p => GenerateColumn(p))));
result.Append(");");
result.AppendLine();
result.AppendFormat("ALTER TABLE {0}.{1} ADD PRIMARY KEY ({2});",
SqlGenerator.QuoteIdentifier(schemaName),
SqlGenerator.QuoteIdentifier(tableName),
string.Join(", ", entitySet.ElementType.KeyMembers.Select(pk => SqlGenerator.QuoteIdentifier(pk.Name))));
result.AppendLine();
yield return result.ToString();
}
}
protected override string DbCreateDatabaseScript(string providerManifestToken,
StoreItemCollection storeItemCollection)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("-- MySql script");
sql.AppendLine("-- Created on " + DateTime.Now);
foreach (EntityContainer container in storeItemCollection.GetItems<EntityContainer>())
{
// now output the tables
foreach (EntitySet es in container.BaseEntitySets.OfType<EntitySet>())
{
sql.Append(GetTableCreateScript(es));
}
// now output the foreign keys
foreach (AssociationSet a in container.BaseEntitySets.OfType<AssociationSet>())
{
sql.Append(GetAssociationCreateScript(a.ElementType));
}
}
return sql.ToString();
}
private IEnumerable<AbstractDatabaseChange> CheckForTableChanges(StoreItemCollection storeItemCollection)
{
foreach (var container in storeItemCollection.GetItems<EntityContainer>())
{
var entitySets = container.BaseEntitySets.OfType<EntitySet>().OrderBy(s => s.Name);
foreach (var entitySet in entitySets)
{
var objectIdentifier = SqlUtil.GetQuotedObjectIdentifierString(entitySet.Table, entitySet.Schema);
var existingObject = _targetConnection
.Query<Sys.Object>("SELECT * FROM sys.objects WHERE object_id= OBJECT_ID(@objectIdentifier)", new { objectIdentifier })
.SingleOrDefault();
if (existingObject == null)
{
// CREATE TABLE
var entityName = TryGetEntityName(entitySet);
yield return new CreateTable(objectIdentifier, entitySet.ElementType.Properties.Select(p => ColumnInfo.FromEF6(p, entitySet.Table)), "Create From Entity: " + entityName);
continue;
}
if (existingObject.type.Trim() == "V")
{
// mapped to a view - we won't scaffold views
continue;
}
var existingColumns = _targetConnection
.Query<Sys.Column>("SELECT columns.*, types.name AS TypeName FROM sys.columns INNER JOIN sys.types ON types.user_type_id = columns.user_type_id WHERE object_id= OBJECT_ID(@objectIdentifier)", new { objectIdentifier }).ToList();
foreach (var property in entitySet.ElementType.Properties)
{
var columnChange = CheckColumn(property, existingColumns, entitySet, objectIdentifier);
if (columnChange != null)
{
yield return columnChange;
}
}
}
}
}
private IEnumerable<AbstractDatabaseChange> CheckForNewSchemas(StoreItemCollection storeItemCollection)
{
var existingSchemas = _targetConnection.Query<string>("SELECT name FROM sys.schemas");
var modelSchemas =
storeItemCollection.GetItems<EntityContainer>()
.SelectMany(c => c.BaseEntitySets.OfType<EntitySet>().Select(s => s.Schema))
.Distinct(StringComparer.OrdinalIgnoreCase)
.OrderBy(x => x);
foreach (var schemaName in modelSchemas)
{
if (!existingSchemas.Contains(schemaName, StringComparer.OrdinalIgnoreCase))
{
yield return new CreateSchema(schemaName);
}
}
}
private IEnumerable<AbstractDatabaseChange> CheckForNewForeignKeyConstraints(StoreItemCollection storeItemCollection)
{
var fkCols = _targetConnection.Query<Sys.ForeignKeyColumn>(Sys.ForeignKeyColumn.SQL_SelectAll).ToLookup(x => x.constraint_object_id);
var allFKs = _targetConnection.Query<Sys.ForeignKey>(Sys.ForeignKey.SQL_SelectAll).ToList();
foreach (var fk in allFKs)
{
fk.Columns.AddRange(fkCols[fk.object_id]);
}
foreach (var container in storeItemCollection.GetItems<EntityContainer>())
{
foreach (var associationSet in container.BaseEntitySets.OfType<AssociationSet>().OrderBy(s => s.Name))
{
var constraint = associationSet.ElementType.ReferentialConstraints.Single();
var principalEnd = associationSet.AssociationSetEnds[constraint.FromRole.Name];
var dependentEnd = associationSet.AssociationSetEnds[constraint.ToRole.Name];
var deleteBehavior = constraint.FromRole.DeleteBehavior;
var parentTableName = dependentEnd.EntitySet.Table;
var parentSchemaName = dependentEnd.EntitySet.Schema;
var parentColumnName = constraint.ToProperties.Single().Name;
var referencedTableName = principalEnd.EntitySet.Table;
var referencedSchemaName = principalEnd.EntitySet.Schema;
var referencedColumnName = constraint.FromProperties.Single().Name;
var fkName = "FK_" + parentTableName + "_" + referencedTableName + "_" + parentColumnName;
if (!allFKs.Exists(fk => fk.Matches(parentTableName, parentSchemaName, parentColumnName, referencedTableName, referencedSchemaName, referencedColumnName)))
{
yield return new AddForeignKey(new ForeignKeyInfo
{
QuotedForeignKeyName = SqlUtil.GetQuotedObjectIdentifierString(fkName),
ParentObjectIdentifier = SqlUtil.GetQuotedObjectIdentifierString(parentTableName, parentSchemaName),
ParentObjectColumns = new[] { parentColumnName },
ReferencedObjectIdentifier = SqlUtil.GetQuotedObjectIdentifierString(referencedTableName, referencedSchemaName),
ReferencedObjectColumns = new[] { referencedColumnName },
CascadeDelete = deleteBehavior == OperationAction.Cascade,
}, "");
}
}
}
}