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