public static string GetSQLIndexField(Table table, TableIndex tableIndex)
{
try
{
var sb = new StringBuilder();
var index = 0;
foreach (var indexColumn in tableIndex.IndexColumnList)
{
var column = table.GeneratedColumns.FirstOrDefault(x => new Guid(x.Key) == indexColumn.FieldID);
sb.Append("[" + column.DatabaseName + "]");
if (index < tableIndex.IndexColumnList.Count - 1)
sb.Append(",");
index++;
}
return sb.ToString();
}
catch (Exception ex)
{
throw;
}
}
private nHydrate.Generator.Models.ModelRoot CreatePOCOModel(nHydrateModel model, Microsoft.VisualStudio.Modeling.Diagrams.Diagram diagram)
{
try
{
var root = new nHydrate.Generator.Models.ModelRoot(null);
root.EnableCustomChangeEvents = model.EmitChangeScripts;
root.CompanyName = model.CompanyName;
root.EmitSafetyScripts = model.EmitSafetyScripts;
root.DefaultNamespace = model.DefaultNamespace;
root.ProjectName = model.ProjectName;
root.SupportLegacySearchObject = false;
root.UseUTCTime = model.UseUTCTime;
root.Version = model.Version;
root.Database.ResetKey(model.Id.ToString());
root.OutputTarget = string.Empty; //model.OutputTarget;
//These have the same mapping values flags so we need convert to int and then convert to the other enumeration
root.TenantColumnName = model.TenantColumnName;
root.TenantPrefix = model.TenantPrefix;
root.Database.CreatedByColumnName = model.CreatedByColumnName;
root.Database.CreatedDateColumnName = model.CreatedDateColumnName;
root.Database.ModifiedByColumnName = model.ModifiedByColumnName;
root.Database.ModifiedDateColumnName = model.ModifiedDateColumnName;
root.Database.TimestampColumnName = model.TimestampColumnName;
root.Database.GrantExecUser = model.GrantUser;
#region Load the entities
foreach (var entity in model.Entities)
{
#region Table Info
var newTable = root.Database.Tables.Add();
newTable.ResetKey(entity.Id.ToString());
newTable.ResetId(HashString(newTable.Key));
newTable.AllowCreateAudit = entity.AllowCreateAudit;
newTable.AllowModifiedAudit = entity.AllowModifyAudit;
newTable.AllowTimestamp = entity.AllowTimestamp;
newTable.AssociativeTable = entity.IsAssociative;
newTable.CodeFacade = entity.CodeFacade;
newTable.DBSchema = entity.Schema;
newTable.Description = entity.Summary;
newTable.Immutable = entity.Immutable;
newTable.TypedTable = (nHydrate.Generator.Models.TypedTableConstants)Enum.Parse(typeof(nHydrate.Generator.Models.TypedTableConstants), entity.TypedEntity.ToString(), true);
newTable.Name = entity.Name;
newTable.GeneratesDoubleDerived = entity.GeneratesDoubleDerived;
newTable.IsTenant = entity.IsTenant;
#endregion
#region Load the fields for this entity
var fieldList = entity.Fields.ToList();
foreach (var field in fieldList.OrderBy(x => x.SortOrder))
{
var newColumn = root.Database.Columns.Add();
newColumn.ResetKey(field.Id.ToString());
newColumn.ResetId(HashString(newColumn.Key));
newColumn.AllowNull = field.Nullable;
newColumn.CodeFacade = field.CodeFacade;
newColumn.ComputedColumn = field.IsCalculated;
newColumn.DataType = (System.Data.SqlDbType)Enum.Parse(typeof(System.Data.SqlDbType), field.DataType.ToString());
newColumn.Default = field.Default;
newColumn.DefaultIsFunc = field.DefaultIsFunc;
newColumn.Description = field.Summary;
newColumn.Formula = field.Formula;
newColumn.Identity = (nHydrate.Generator.Models.IdentityTypeConstants)Enum.Parse(typeof(nHydrate.Generator.Models.IdentityTypeConstants), field.Identity.ToString());
newColumn.IsIndexed = field.IsIndexed;
newColumn.IsReadOnly = field.IsReadOnly;
newColumn.IsUnique = field.IsUnique;
newColumn.Length = field.Length;
newColumn.Name = field.Name;
newColumn.ParentTableRef = newTable.CreateRef(newTable.Key);
newColumn.PrimaryKey = field.IsPrimaryKey;
newColumn.Scale = field.Scale;
newColumn.SortOrder = field.SortOrder;
newColumn.Obsolete = field.Obsolete;
newTable.Columns.Add(newColumn.CreateRef(newColumn.Key));
}
#endregion
#region Indexes
var indexList = entity.Indexes.ToList();
foreach (var index in indexList)
{
var indexColumns = index.IndexColumns.Where(x => x.GetField() != null).ToList();
if (indexColumns.Count > 0)
{
var newIndex = new nHydrate.Generator.Models.TableIndex(newTable.Root)
{
Description = index.Summary,
IsUnique = index.IsUnique,
Clustered = index.Clustered,
PrimaryKey = (index.IndexType == IndexTypeConstants.PrimaryKey)
};
newTable.TableIndexList.Add(newIndex);
newIndex.ResetKey(index.Id.ToString());
newIndex.ResetId(HashString(newIndex.Key));
newIndex.ImportedName = index.ImportedName;
//Add index columns
foreach (var ic in indexColumns.OrderBy(x => x.SortOrder).ThenBy(x => x.GetField().Name))
{
var field = ic.GetField();
var newColumn = new nHydrate.Generator.Models.TableIndexColumn(newTable.Root)
{
Ascending = ic.Ascending, FieldID = field.Id
};
newIndex.IndexColumnList.Add(newColumn);
}
}
}
#endregion
#region Static Data
//Determine how many rows there are
var orderKeyList = entity.StaticDatum.Select(x => x.OrderKey).Distinct().ToList();
var rowCount = orderKeyList.Count;
//Create a OLD static data row for each one
for (var ii = 0; ii < rowCount; ii++)
{
//For each row create N cells one for each column
var rowEntry = new nHydrate.Generator.Models.RowEntry(newTable.Root);
var staticDataFieldList = fieldList.Where(x => !x.DataType.IsBinaryType() && x.DataType != DataTypeConstants.Timestamp).ToList();
for (var jj = 0; jj < staticDataFieldList.Count; jj++)
{
var cellEntry = new nHydrate.Generator.Models.CellEntry(newTable.Root);
var column = newTable.GetColumns().ToList()[jj];
cellEntry.ColumnRef = column.CreateRef(column.Key);
var currentColumn = fieldList.FirstOrDefault(x => x.Id == new Guid(column.Key));
if (currentColumn != null)
{
var dataum = entity.StaticDatum.FirstOrDefault(x =>
x.ColumnKey == currentColumn.Id &&
x.OrderKey == orderKeyList[ii]);
if (dataum != null)
{
cellEntry.Value = dataum.Value;
cellEntry.ResetKey(dataum.Id.ToString());
}
//Add the cell to the row
rowEntry.CellEntries.Add(cellEntry);
}
}
newTable.StaticData.Add(rowEntry);
}
#endregion
}
#endregion
#region Relations
{
var relationConnectors = diagram.NestedChildShapes.Where(x => x is EntityAssociationConnector).Cast <EntityAssociationConnector>().ToList();
foreach (var shape in relationConnectors)
{
if (shape is EntityAssociationConnector)
{
var connector = shape as EntityAssociationConnector;
var parent = connector.FromShape.ModelElement as Entity;
var child = connector.ToShape.ModelElement as Entity;
var relation = connector.ModelElement as EntityHasEntities;
var fieldList = model.RelationFields.Where(x => x.RelationID == relation.Id);
var parentTable = root.Database.Tables.FirstOrDefault(x => x.Name == parent.Name);
var childTable = root.Database.Tables.FirstOrDefault(x => x.Name == child.Name);
//If we found both parent and child tables...
if (parentTable != null && childTable != null && !childTable.IsInheritedFrom(parentTable))
{
var isValidRelation = true;
if (isValidRelation)
{
var newRelation = root.Database.Relations.Add();
newRelation.ResetKey((connector.ModelElement as nHydrate.Dsl.EntityHasEntities).InternalId.ToString());
newRelation.ResetId(HashString(newRelation.Key));
newRelation.ParentTableRef = parentTable.CreateRef(parentTable.Key);
newRelation.ChildTableRef = childTable.CreateRef(childTable.Key);
newRelation.RoleName = ((EntityHasEntities)connector.ModelElement).RoleName;
switch (relation.DeleteAction)
{
case DeleteActionConstants.Cascade:
newRelation.DeleteAction = Relation.DeleteActionConstants.Cascade;
break;
case DeleteActionConstants.NoAction:
newRelation.DeleteAction = Relation.DeleteActionConstants.NoAction;
break;
case DeleteActionConstants.SetNull:
newRelation.DeleteAction = Relation.DeleteActionConstants.SetNull;
break;
}
newRelation.Enforce = relation.IsEnforced;
//Create the column links
foreach (var columnSet in fieldList)
{
var field1 = parent.Fields.FirstOrDefault(x => x.Id == columnSet.SourceFieldId);
var field2 = child.Fields.FirstOrDefault(x => x.Id == columnSet.TargetFieldId);
var column1 = parentTable.GetColumnsFullHierarchy().FirstOrDefault(x => x.Name == field1.Name);
var column2 = childTable.GetColumnsFullHierarchy().FirstOrDefault(x => x.Name == field2.Name);
newRelation.ColumnRelationships.Add(new nHydrate.Generator.Models.ColumnRelationship(root)
{
ParentColumnRef = column1.CreateRef(column1.Key),
ChildColumnRef = column2.CreateRef(column2.Key),
}
);
}
//Actually add the relation to the collection
if (newRelation.ColumnRelationships.Count > 0)
{
parentTable.Relationships.Add(newRelation.CreateRef(newRelation.Key));
}
}
}
}
}
} //inner block
#endregion
#region Views
foreach (var view in model.Views)
{
var newView = root.Database.CustomViews.Add();
newView.ResetKey(view.Id.ToString());
newView.ResetId(HashString(newView.Key));
newView.CodeFacade = view.CodeFacade;
newView.DBSchema = view.Schema;
newView.Description = view.Summary;
newView.Name = view.Name;
newView.SQL = view.SQL;
newView.GeneratesDoubleDerived = view.GeneratesDoubleDerived;
foreach (var field in view.Fields)
{
var newField = root.Database.CustomViewColumns.Add();
newField.ResetKey(field.Id.ToString());
newField.ResetId(HashString(newField.Key));
newField.AllowNull = field.Nullable;
newField.CodeFacade = field.CodeFacade;
newField.DataType = (System.Data.SqlDbType)Enum.Parse(typeof(System.Data.SqlDbType), field.DataType.ToString());
newField.Default = field.Default;
newField.Description = field.Summary;
newField.IsPrimaryKey = field.IsPrimaryKey;
newField.Length = field.Length;
newField.Name = field.Name;
newField.Scale = field.Scale;
newView.Columns.Add(newField.CreateRef(newField.Key));
newField.ParentViewRef = newView.CreateRef(newView.Key);
}
}
#endregion
return(root);
}
catch (Exception ex)
{
throw;
}
}
public static string GetSQLDropIndex(Table table, TableIndex index)
{
var sb = new StringBuilder();
var model = table.Root as ModelRoot;
var tableName = Globals.GetTableDatabaseName(model, table);
var columnList = GetIndexColumns(table, index);
var indexName = GetIndexName(table, index);
if (columnList.Count > 0)
{
sb.AppendLine("--DELETE INDEX");
sb.AppendLine("if exists(select * from sys.indexes where name = '" + indexName + "')");
sb.AppendLine("DROP INDEX [" + indexName + "] ON [" + table.GetSQLSchema() + "].[" + tableName + "]");
}
return sb.ToString();
}
public static string GetSQLCreateIndex(Table table, TableIndex index, bool includeDrop)
{
var sb = new StringBuilder();
var model = table.Root as ModelRoot;
var tableName = Globals.GetTableDatabaseName(model, table);
var columnList = GetIndexColumns(table, index);
var indexName = GetIndexName(table, index);
if (columnList.Count > 0)
{
if (includeDrop)
{
//If this is to be a clustered index then check if it exists and is non-clustered and remove it
//If this is to be a non-clustered index then check if it exists and is clustered and remove it
sb.AppendLine("--DELETE INDEX");
if (index.Clustered)
{
sb.AppendLine("if exists(select * from sys.indexes where name = '" + indexName + "' and type_desc = 'NONCLUSTERED')");
sb.AppendLine("DROP INDEX [" + indexName + "] ON [" + table.GetSQLSchema() + "].[" + tableName + "]");
sb.AppendLine("GO");
}
else
{
sb.AppendLine("if exists(select * from sys.indexes where name = '" + indexName + "' and type_desc = 'CLUSTERED')");
sb.AppendLine("DROP INDEX [" + indexName + "] ON [" + table.GetSQLSchema() + "].[" + tableName + "]");
sb.AppendLine("GO");
}
sb.AppendLine();
}
//Do not create unique index for PK (it is already unique)
if (!index.PrimaryKey)
{
var checkSqlList = new List<string>();
foreach(var c in columnList)
{
checkSqlList.Add("exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + c.Value.DatabaseName + "' and o.name = '" + table.DatabaseName + "')");
}
sb.AppendLine("--INDEX FOR TABLE [" + table.DatabaseName + "] COLUMNS:" + string.Join(", ", columnList.Select(x => "[" + x.Value.DatabaseName + "]")));
sb.AppendLine("if not exists(select * from sys.indexes where name = '" + indexName + "') and " + string.Join(" and ", checkSqlList));
sb.Append("CREATE " + (index.IsUnique ? "UNIQUE " : string.Empty) + (index.Clustered ? "CLUSTERED " : "NONCLUSTERED ") + "INDEX [" + indexName + "] ON [" + table.GetSQLSchema() + "].[" + tableName + "] (");
sb.Append(string.Join(",", columnList.Select(x => "[" + x.Value.DatabaseName + "] " + (x.Key.Ascending ? "ASC" : "DESC"))));
sb.AppendLine(")");
}
}
return sb.ToString();
}
public static string GetIndexName(Table table, TableIndex index)
{
//Make sure that the index name is the same each time
var columnList = GetIndexColumns(table, index);
var prefix = (index.PrimaryKey ? "PK" : "IDX");
var indexName = prefix + "_" + table.Name.Replace("-", "") + "_" + string.Join("_", columnList.Select(x => x.Value.Name));
indexName = indexName.ToUpper();
return indexName;
}
public static Dictionary<TableIndexColumn, Column> GetIndexColumns(Table table, TableIndex index)
{
var columnList = new Dictionary<TableIndexColumn, Column>();
foreach (var indexColumn in index.IndexColumnList)
{
var column = table.GeneratedColumns.FirstOrDefault(x => new Guid(x.Key) == indexColumn.FieldID);
if (column != null)
columnList.Add(indexColumn, column);
}
return columnList;
}
private nHydrate.Generator.Models.ModelRoot CreatePOCOModel(nHydrateModel model, Microsoft.VisualStudio.Modeling.Diagrams.Diagram diagram, Module ownerModule)
{
try
{
var root = new nHydrate.Generator.Models.ModelRoot(null);
root.TransformNames = model.TransformNames;
root.EnableCustomChangeEvents = model.EmitChangeScripts;
root.CompanyName = model.CompanyName;
root.EmitSafetyScripts = model.EmitSafetyScripts;
root.Copyright = model.Copyright;
root.DefaultNamespace = model.DefaultNamespace;
root.ProjectName = model.ProjectName;
root.SQLServerType = (SQLServerTypeConstants)Enum.Parse(typeof(SQLServerTypeConstants), model.SQLServerType.ToString());
root.EFVersion = (nHydrate.Generator.Common.GeneratorFramework.EFVersionConstants)Enum.Parse(typeof(nHydrate.Generator.Common.GeneratorFramework.EFVersionConstants), model.EFVersion.ToString());
root.SupportLegacySearchObject = false;
root.UseUTCTime = model.UseUTCTime;
root.Version = model.Version;
root.Database.UseGeneratedCRUD = model.UseGeneratedCRUD;
root.Database.ResetKey(model.Id.ToString());
root.OutputTarget = string.Empty; //model.OutputTarget;
//These have the same mapping values flags so we need convert to int and then convert to the other enumeration
root.TenantColumnName = model.TenantColumnName;
root.TenantPrefix = model.TenantPrefix;
root.AllowMocks = model.AllowMocks;
foreach (var md in model.ModelMetadata)
{
var newmd = new Generator.Models.MetadataItem();
newmd.Key = md.Key;
newmd.Value = md.Value;
root.MetaData.Add(newmd);
}
#region Set Refactorizations
foreach (var r in model.Refactorizations)
{
if (r is nHydrate.Dsl.Objects.RefactorTableSplit)
{
var newR = new nHydrate.Generator.Common.GeneratorFramework.RefactorTableSplit();
newR.EntityKey1 = (r as nHydrate.Dsl.Objects.RefactorTableSplit).EntityKey1;
newR.EntityKey2 = (r as nHydrate.Dsl.Objects.RefactorTableSplit).EntityKey2;
var flist = (r as nHydrate.Dsl.Objects.RefactorTableSplit).ReMappedFieldIDList;
foreach (var k in flist.Keys)
newR.ReMappedFieldIDList.Add(k, flist[k]);
root.Refactorizations.Add(newR);
}
else if (r is nHydrate.Dsl.Objects.RefactorTableCombine)
{
var newR = new nHydrate.Generator.Common.GeneratorFramework.RefactorTableCombine();
newR.EntityKey1 = (r as nHydrate.Dsl.Objects.RefactorTableCombine).EntityKey1;
newR.EntityKey2 = (r as nHydrate.Dsl.Objects.RefactorTableCombine).EntityKey2;
var flist = (r as nHydrate.Dsl.Objects.RefactorTableCombine).ReMappedFieldIDList;
foreach (var k in flist.Keys)
newR.ReMappedFieldIDList.Add(k, flist[k]);
root.Refactorizations.Add(newR);
}
}
#endregion
if (ownerModule != null)
root.ModuleName = ownerModule.Name;
root.Database.CreatedByColumnName = model.CreatedByColumnName;
root.Database.CreatedDateColumnName = model.CreatedDateColumnName;
root.Database.ModifiedByColumnName = model.ModifiedByColumnName;
root.Database.ModifiedDateColumnName = model.ModifiedDateColumnName;
root.Database.TimestampColumnName = model.TimestampColumnName;
root.Database.GrantExecUser = model.GrantUser;
root.Database.Collate = model.Collate;
root.Database.PrecedenceOrderList = PrecedenceUtil.GetAllPrecedenceItems(model)
.Select(x => x.ID)
.ToList();
var inheritanceConnectors = diagram.NestedChildShapes.Where(x => x is EntityInheritanceConnector).Cast<EntityInheritanceConnector>().ToList();
#region Load the entities
foreach (var entity in model.Entities.Where(x => x.IsGenerated).Where(x => ownerModule == null || x.Modules.Contains(ownerModule)))
{
#region Table Info
var newTable = root.Database.Tables.Add();
newTable.ResetKey(entity.Id.ToString());
newTable.ResetId(HashString(newTable.Key));
newTable.AllowAuditTracking = entity.AllowAuditTracking;
newTable.AllowCreateAudit = entity.AllowCreateAudit;
newTable.AllowModifiedAudit = entity.AllowModifyAudit;
newTable.AllowTimestamp = entity.AllowTimestamp;
newTable.AssociativeTable = entity.IsAssociative;
newTable.CodeFacade = entity.CodeFacade;
newTable.DBSchema = entity.Schema;
newTable.Description = entity.Summary;
newTable.EnforcePrimaryKey = entity.EnforcePrimaryKey;
newTable.Generated = entity.IsGenerated;
newTable.Immutable = entity.Immutable;
newTable.TypedTable = (nHydrate.Generator.Models.TypedTableConstants)Enum.Parse(typeof(nHydrate.Generator.Models.TypedTableConstants), entity.TypedEntity.ToString(), true);
newTable.Name = entity.Name;
newTable.GeneratesDoubleDerived = entity.GeneratesDoubleDerived;
newTable.IsTenant = entity.IsTenant;
//Add metadata
foreach (var md in entity.EntityMetadata)
{
newTable.MetaData.Add(new nHydrate.Generator.Models.MetadataItem() { Key = md.Key, Value = md.Value });
}
if (entity.SecurityFunction != null)
{
newTable.Security.ResetKey(entity.SecurityFunction.Id.ToString());
newTable.Security.SQL = entity.SecurityFunction.SQL;
//Just in case these are ordered get all sort-ordered parameters first then take on all unordred alphabetized parmameters
var orderedParameters = entity.SecurityFunction.SecurityFunctionParameters.Where(x => x.SortOrder > 0).OrderBy(x => x.SortOrder).ToList();
orderedParameters.AddRange(entity.SecurityFunction.SecurityFunctionParameters.Where(x => x.SortOrder == 0).OrderBy(x => x.Name).ToList());
foreach (var parameter in orderedParameters)
{
var newParameter = root.Database.FunctionParameters.Add();
newParameter.ResetKey(parameter.Id.ToString());
newParameter.ResetId(HashString(newParameter.Key));
newParameter.ParentTableRef = newTable.CreateRef(newTable.Key);
newParameter.AllowNull = parameter.Nullable;
newParameter.CodeFacade = parameter.CodeFacade;
newParameter.DataType = (System.Data.SqlDbType)Enum.Parse(typeof(System.Data.SqlDbType), parameter.DataType.ToString());
newParameter.Default = parameter.Default;
newParameter.Description = parameter.Summary;
newParameter.Generated = parameter.IsGenerated;
newParameter.Length = parameter.Length;
newParameter.Name = parameter.Name;
newParameter.Scale = parameter.Scale;
newParameter.SortOrder = parameter.SortOrder;
var r = newParameter.CreateRef(newParameter.Key);
r.RefType = nHydrate.Generator.Models.ReferenceType.FunctionParameter;
newTable.Security.Parameters.Add(r);
}
}
#endregion
#region Load the fields for this entity
var fieldList = entity.Fields.Where(x => x.IsGenerated).Where(x => ownerModule == null || x.Modules.Contains(ownerModule)).ToList();
foreach (var field in fieldList.OrderBy(x => x.SortOrder))
{
var newColumn = root.Database.Columns.Add();
newColumn.ResetKey(field.Id.ToString());
newColumn.ResetId(HashString(newColumn.Key));
newColumn.AllowNull = field.Nullable;
newColumn.Collate = field.Collate;
newColumn.CodeFacade = field.CodeFacade;
newColumn.ComputedColumn = field.IsCalculated;
newColumn.DataType = (System.Data.SqlDbType)Enum.Parse(typeof(System.Data.SqlDbType), field.DataType.ToString());
newColumn.Default = field.Default;
newColumn.DefaultIsFunc = field.DefaultIsFunc;
newColumn.Description = field.Summary;
newColumn.Formula = field.Formula;
newColumn.FriendlyName = field.FriendlyName;
newColumn.Generated = field.IsGenerated;
newColumn.Identity = (nHydrate.Generator.Models.IdentityTypeConstants)Enum.Parse(typeof(nHydrate.Generator.Models.IdentityTypeConstants), field.Identity.ToString());
newColumn.IsIndexed = field.IsIndexed;
newColumn.IsReadOnly = field.IsReadOnly;
newColumn.IsUnique = field.IsUnique;
newColumn.Length = field.Length;
newColumn.Max = field.Max;
newColumn.Min = field.Min;
newColumn.Name = field.Name;
newColumn.ParentTableRef = newTable.CreateRef(newTable.Key);
newColumn.PrimaryKey = field.IsPrimaryKey;
newColumn.Scale = field.Scale;
newColumn.ValidationExpression = field.ValidationExpression;
newColumn.IsBrowsable = field.IsBrowsable;
newColumn.Category = field.Category;
newColumn.SortOrder = field.SortOrder;
newColumn.Mask = field.DataFormatString;
newColumn.UIDataType = (System.ComponentModel.DataAnnotations.DataType)Enum.Parse(typeof(System.ComponentModel.DataAnnotations.DataType), field.UIDataType.ToString(), true);
newColumn.Obsolete = field.Obsolete;
newTable.Columns.Add(newColumn.CreateRef(newColumn.Key));
//Add metadata
foreach (var md in field.FieldMetadata)
{
newColumn.MetaData.Add(new nHydrate.Generator.Models.MetadataItem() { Key = md.Key, Value = md.Value });
}
}
#endregion
#region Indexes
//Find all index for this entity in module
List<Index> indexList = null;
if (ownerModule != null)
{
indexList = new List<Index>();
indexList.AddRange(entity.Indexes.Where(x => x.IndexType == IndexTypeConstants.PrimaryKey).ToList());
foreach (var im in model.IndexModules.Where(x => x.ModuleId == ownerModule.Id))
{
indexList.AddRange(entity.Indexes.Where(x => x.IndexType != IndexTypeConstants.PrimaryKey && x.Id == im.IndexID));
}
}
else
{
indexList = entity.Indexes.ToList();
}
foreach (var index in indexList)
{
var indexColumns = index.IndexColumns.Where(x => x.GetField() != null && (ownerModule == null || x.GetField().Modules.Contains(ownerModule))).ToList();
if (indexColumns.Count > 0)
{
var newIndex = new nHydrate.Generator.Models.TableIndex(newTable.Root)
{
Description = index.Summary,
IsUnique = index.IsUnique,
Clustered = index.Clustered,
PrimaryKey = (index.IndexType == IndexTypeConstants.PrimaryKey)
};
newTable.TableIndexList.Add(newIndex);
newIndex.ResetKey(index.Id.ToString());
newIndex.ResetId(HashString(newIndex.Key));
newIndex.ImportedName = index.ImportedName;
//Add index columns
foreach (var ic in indexColumns.OrderBy(x => x.SortOrder).ThenBy(x => x.GetField().Name))
{
var field = ic.GetField();
var newColumn = new nHydrate.Generator.Models.TableIndexColumn(newTable.Root) { Ascending = ic.Ascending, FieldID = field.Id };
newIndex.IndexColumnList.Add(newColumn);
}
}
}
#endregion
#region Composites
foreach (var composite in entity.Composites.Where(x => x.IsGenerated))
{
var newComponent = new nHydrate.Generator.Models.TableComponent(newTable.ComponentList.Root, newTable.ComponentList.Parent);
newComponent.ResetKey(composite.Id.ToString());
//newComponent.ResetId(HashString(newComponent.Key));
newComponent.CodeFacade = composite.CodeFacade;
newComponent.Description = composite.Summary;
newComponent.Generated = composite.IsGenerated;
newComponent.Name = composite.Name;
newTable.ComponentList.Add(newComponent);
foreach (var column in composite.Fields)
{
var oldColumn = entity.Fields.FirstOrDefault(x => x.Id == column.Id);
if (oldColumn != null)
{
var newColumn = newTable.GetColumns().FirstOrDefault(x => x.Name == oldColumn.Name);
if (newColumn != null)
{
newColumn.ResetKey(column.Id.ToString());
newColumn.ResetId(HashString(newColumn.Key));
newComponent.Columns.Add(newColumn.CreateRef(newColumn.Key));
}
}
}
}
#endregion
#region Static Data
//if (entity.TypedEntity != TypedEntityConstants.None)
//{
//Determine how many rows there are
var orderKeyList = entity.StaticDatum.Select(x => x.OrderKey).Distinct().ToList();
var rowCount = orderKeyList.Count();
//Create a OLD static data row for each one
for (var ii = 0; ii < rowCount; ii++)
{
//For each row create N cells one for each column
var rowEntry = new nHydrate.Generator.Models.RowEntry(newTable.Root);
var staticDataFieldList = fieldList.Where(x => !x.IsBinaryType() && x.DataType != DataTypeConstants.Timestamp).ToList();
for (var jj = 0; jj < staticDataFieldList.Count; jj++)
{
var cellEntry = new nHydrate.Generator.Models.CellEntry(newTable.Root);
var column = newTable.GetColumns().ToList()[jj];
cellEntry.ColumnRef = column.CreateRef(column.Key);
var currentColumn = fieldList.FirstOrDefault(x => x.Id == new Guid(column.Key));
if (currentColumn != null)
{
var dataum = entity.StaticDatum.FirstOrDefault(x =>
x.ColumnKey == currentColumn.Id &&
x.OrderKey == orderKeyList[ii]);
if (dataum != null)
{
cellEntry.Value = dataum.Value;
cellEntry.ResetKey(dataum.Id.ToString());
//cellEntry.ResetId(HashString(cellEntry.Key));
}
//Add the cell to the row
rowEntry.CellEntries.Add(cellEntry);
}
}
newTable.StaticData.Add(rowEntry);
}
//}
#endregion
}
//Setup inheritance
foreach (var connector in inheritanceConnectors)
{
var d = connector.FromShape.ModelElement as Entity;
var b = connector.ToShape.ModelElement as Entity;
var baseTable = root.Database.Tables.FirstOrDefault(x => x.Name == b.Name);
var derivedTable = root.Database.Tables.FirstOrDefault(x => x.Name == d.Name);
if (d != null && b != null && derivedTable != null && baseTable != null)
{
derivedTable.ParentTable = baseTable;
}
}
#endregion
#region Relations
{
var relationConnectors = diagram.NestedChildShapes.Where(x => x is EntityAssociationConnector).Cast<EntityAssociationConnector>().ToList();
foreach (var shape in relationConnectors)
{
if (shape is EntityAssociationConnector)
{
var connector = shape as EntityAssociationConnector;
var parent = connector.FromShape.ModelElement as Entity;
var child = connector.ToShape.ModelElement as Entity;
var relation = connector.ModelElement as EntityHasEntities;
var fieldList = model.RelationFields.Where(x => x.RelationID == relation.Id);
var parentTable = root.Database.Tables.FirstOrDefault(x => x.Name == parent.Name);
var childTable = root.Database.Tables.FirstOrDefault(x => x.Name == child.Name);
//If we found both parent and child tables...
if (parentTable != null && childTable != null && !childTable.IsInheritedFrom(parentTable))
{
var isValidRelation = true;
if (ownerModule != null)
{
isValidRelation = ((IModuleLink)relation).Modules.Contains(ownerModule);
}
if (model.UseModules && isValidRelation)
{
//If using modules then check that this relation's fields are in this module
foreach (var columnSet in fieldList)
{
var field1 = parent.Fields.FirstOrDefault(x => x.Id == columnSet.SourceFieldId);
var field2 = child.Fields.FirstOrDefault(x => x.Id == columnSet.TargetFieldId);
if (!field1.Modules.Contains(ownerModule)) isValidRelation = false;
if (!field2.Modules.Contains(ownerModule)) isValidRelation = false;
}
}
if (isValidRelation)
{
var newRelation = root.Database.Relations.Add();
newRelation.ResetKey((connector.ModelElement as nHydrate.Dsl.EntityHasEntities).InternalId.ToString());
newRelation.ResetId(HashString(newRelation.Key));
newRelation.ParentTableRef = parentTable.CreateRef(parentTable.Key);
newRelation.ChildTableRef = childTable.CreateRef(childTable.Key);
newRelation.RoleName = ((EntityHasEntities)connector.ModelElement).RoleName;
if (ownerModule == null)
{
newRelation.Enforce = relation.IsEnforced;
}
else
{
var relationModule = model.RelationModules.FirstOrDefault(x => x.RelationID == relation.Id && x.ModuleId == ownerModule.Id);
if (relationModule == null)
{
//I do not think this should ever happen??
newRelation.Enforce = false;
}
else
{
newRelation.Enforce = relationModule.IsEnforced;
}
}
//Create the column links
foreach (var columnSet in fieldList)
{
var field1 = parent.Fields.FirstOrDefault(x => x.Id == columnSet.SourceFieldId);
var field2 = child.Fields.FirstOrDefault(x => x.Id == columnSet.TargetFieldId);
var column1 = parentTable.GetColumnsFullHierarchy().FirstOrDefault(x => x.Name == field1.Name);
var column2 = childTable.GetColumnsFullHierarchy().FirstOrDefault(x => x.Name == field2.Name);
newRelation.ColumnRelationships.Add(new nHydrate.Generator.Models.ColumnRelationship(root)
{
ParentColumnRef = column1.CreateRef(column1.Key),
ChildColumnRef = column2.CreateRef(column2.Key),
}
);
}
//Actually add the relation to the collection
if (newRelation.ColumnRelationships.Count > 0)
parentTable.Relationships.Add(newRelation.CreateRef(newRelation.Key));
}
else
{
System.Diagnostics.Debug.Write(string.Empty);
}
}
else
{
System.Diagnostics.Debug.Write(string.Empty);
}
}
}
//Setup inheritance relationships
foreach (var connector in inheritanceConnectors)
{
var b = connector.ToShape.ModelElement as Entity;
var d = connector.FromShape.ModelElement as Entity;
var baseTable = root.Database.Tables.FirstOrDefault(x => x.Name == b.Name);
var derivedTable = root.Database.Tables.FirstOrDefault(x => x.Name == d.Name);
if (derivedTable != null && baseTable != null)
{
var newRelation = root.Database.Relations.Add();
newRelation.ResetKey((connector.ModelElement as EntityInheritsEntity).InternalId.ToString());
newRelation.ParentTableRef = baseTable.CreateRef(baseTable.Key);
newRelation.ChildTableRef = derivedTable.CreateRef(derivedTable.Key);
newRelation.Enforce = true; //Hard-wired for now
//Loop through the primary keys of each table and map
foreach (var pk in baseTable.PrimaryKeyColumns)
{
var fk = derivedTable.GetColumns().FirstOrDefault(x => x.Name == pk.Name);
if (fk != null)
{
newRelation.ColumnRelationships.Add(new nHydrate.Generator.Models.ColumnRelationship(root)
{
ParentColumnRef = pk.CreateRef(pk.Key),
ChildColumnRef = fk.CreateRef(fk.Key),
});
}
}
}
}
} //inner block
#endregion
#region Views
foreach (var view in model.Views.Where(x => x.IsGenerated).Where(x => ownerModule == null || x.Modules.Contains(ownerModule)))
{
var newView = root.Database.CustomViews.Add();
newView.ResetKey(view.Id.ToString());
newView.ResetId(HashString(newView.Key));
newView.CodeFacade = view.CodeFacade;
newView.DBSchema = view.Schema;
newView.Description = view.Summary;
newView.Generated = view.IsGenerated;
newView.Name = view.Name;
newView.SQL = view.SQL;
newView.GeneratesDoubleDerived = view.GeneratesDoubleDerived;
newView.PrecedenceOrder = view.PrecedenceOrder;
foreach (var field in view.Fields)
{
var newField = root.Database.CustomViewColumns.Add();
newField.ResetKey(field.Id.ToString());
newField.ResetId(HashString(newField.Key));
newField.AllowNull = field.Nullable;
newField.CodeFacade = field.CodeFacade;
newField.DataType = (System.Data.SqlDbType)Enum.Parse(typeof(System.Data.SqlDbType), field.DataType.ToString());
newField.Default = field.Default;
newField.Description = field.Summary;
newField.FriendlyName = field.FriendlyName;
newField.IsPrimaryKey = field.IsPrimaryKey;
newField.Generated = field.IsGenerated;
newField.Length = field.Length;
newField.Name = field.Name;
newField.Scale = field.Scale;
newView.Columns.Add(newField.CreateRef(newField.Key));
newField.ParentViewRef = newView.CreateRef(newView.Key);
}
}
#endregion
#region View Relations
{
var relationConnectors = diagram.NestedChildShapes.Where(x => x is EntityViewAssociationConnector).Cast<EntityViewAssociationConnector>().ToList();
foreach (var shape in relationConnectors)
{
if (shape is EntityViewAssociationConnector)
{
var connector = shape as EntityViewAssociationConnector;
var parent = connector.FromShape.ModelElement as Entity;
var child = connector.ToShape.ModelElement as nHydrate.Dsl.View;
var relation = connector.ModelElement as EntityHasViews;
var fieldList = model.RelationFields.Where(x => x.RelationID == relation.Id);
var parentTable = root.Database.Tables.FirstOrDefault(x => x.Name == parent.Name);
var childTable = root.Database.CustomViews.FirstOrDefault(x => x.Name == child.Name);
//If we found both parent and child tables...
if (parentTable != null && childTable != null)
{
var isValidRelation = true;
if (ownerModule != null)
{
isValidRelation = ((IModuleLink)relation).Modules.Contains(ownerModule);
}
if (model.UseModules && isValidRelation)
{
//If using modules then check that this relation's fields are in this module
foreach (var columnSet in fieldList)
{
var field1 = parent.Fields.FirstOrDefault(x => x.Id == columnSet.SourceFieldId);
var field2 = child.Fields.FirstOrDefault(x => x.Id == columnSet.TargetFieldId);
if (!field1.Modules.Contains(ownerModule)) isValidRelation = false;
}
}
if (isValidRelation)
{
var newRelation = root.Database.ViewRelations.Add();
newRelation.ParentTableRef = parentTable.CreateRef(parentTable.Key);
newRelation.ChildViewRef = childTable.CreateRef(childTable.Key);
newRelation.RoleName = ((EntityHasViews)connector.ModelElement).RoleName;
//Create the column links
foreach (var columnSet in fieldList)
{
var field1 = parent.Fields.FirstOrDefault(x => x.Id == columnSet.SourceFieldId);
var field2 = child.Fields.FirstOrDefault(x => x.Id == columnSet.TargetFieldId);
var column1 = parentTable.GetColumnsFullHierarchy().FirstOrDefault(x => x.Name == field1.Name);
var column2 = childTable.GetColumns().FirstOrDefault(x => x.Name == field2.Name);
newRelation.ColumnRelationships.Add(new nHydrate.Generator.Models.ViewColumnRelationship(root)
{
ParentColumnRef = column1.CreateRef(column1.Key),
ChildColumnRef = column2.CreateRef(column2.Key),
}
);
}
//Actually add the relation to the collection
if (newRelation.ColumnRelationships.Count > 0)
parentTable.ViewRelationships.Add(newRelation.CreateRef(newRelation.Key));
}
else
{
System.Diagnostics.Debug.Write(string.Empty);
}
}
else
{
System.Diagnostics.Debug.Write(string.Empty);
}
}
}
} //inner block
#endregion
#region Stored Procedures
foreach (var storedProc in model.StoredProcedures.Where(x => x.IsGenerated).Where(x => ownerModule == null || x.Modules.Contains(ownerModule)))
{
var newStoredProc = root.Database.CustomStoredProcedures.Add();
newStoredProc.ResetKey(storedProc.Id.ToString());
newStoredProc.ResetId(HashString(newStoredProc.Key));
newStoredProc.CodeFacade = storedProc.CodeFacade;
newStoredProc.DatabaseObjectName = storedProc.DatabaseObjectName;
newStoredProc.DBSchema = storedProc.Schema;
newStoredProc.Description = storedProc.Summary;
newStoredProc.Generated = storedProc.IsGenerated;
newStoredProc.Name = storedProc.Name;
newStoredProc.SQL = storedProc.SQL;
newStoredProc.GeneratesDoubleDerived = storedProc.GeneratesDoubleDerived;
newStoredProc.PrecedenceOrder = storedProc.PrecedenceOrder;
newStoredProc.IsExisting = storedProc.IsExisting;
foreach (var field in storedProc.Fields)
{
var newField = root.Database.CustomStoredProcedureColumns.Add();
newField.ResetKey(field.Id.ToString());
newField.ResetId(HashString(newField.Key));
newField.AllowNull = field.Nullable;
newField.CodeFacade = field.CodeFacade;
newField.DataType = (System.Data.SqlDbType)Enum.Parse(typeof(System.Data.SqlDbType), field.DataType.ToString());
newField.Default = field.Default;
newField.Description = field.Summary;
newField.FriendlyName = field.FriendlyName;
newField.Generated = field.IsGenerated;
newField.Length = field.Length;
newField.Name = field.Name;
newField.Scale = field.Scale;
newStoredProc.Columns.Add(newField.CreateRef(newField.Key));
newField.ParentRef = newStoredProc.CreateRef(newStoredProc.Key);
}
//Just in case these are ordered get all sort-ordered parameters first then take on all unordred alphabetized parmameters
var orderedParameters = storedProc.Parameters.Where(x => x.SortOrder > 0).OrderBy(x => x.SortOrder).ToList();
orderedParameters.AddRange(storedProc.Parameters.Where(x => x.SortOrder == 0).OrderBy(x => x.Name).ToList());
foreach (var parameter in orderedParameters)
{
var newParameter = root.Database.CustomRetrieveRuleParameters.Add();
newParameter.ResetKey(parameter.Id.ToString());
newParameter.ResetId(HashString(newParameter.Key));
newParameter.AllowNull = parameter.Nullable;
newParameter.CodeFacade = parameter.CodeFacade;
newParameter.DataType = (System.Data.SqlDbType)Enum.Parse(typeof(System.Data.SqlDbType), parameter.DataType.ToString());
newParameter.Default = parameter.Default;
newParameter.Description = parameter.Summary;
newParameter.Generated = parameter.IsGenerated;
newParameter.IsOutputParameter = parameter.IsOutputParameter;
newParameter.Length = parameter.Length;
newParameter.Name = parameter.Name;
newParameter.Scale = parameter.Scale;
newParameter.SortOrder = parameter.SortOrder;
newStoredProc.Parameters.Add(newParameter.CreateRef(newParameter.Key));
newParameter.ParentTableRef = newStoredProc.CreateRef(newStoredProc.Key);
}
}
#endregion
#region Functions
foreach (var function in model.Functions.Where(x => x.IsGenerated).Where(x => ownerModule == null || x.Modules.Contains(ownerModule)))
{
var newFunction = root.Database.Functions.Add();
newFunction.ResetKey(function.Id.ToString());
newFunction.ResetId(HashString(newFunction.Key));
newFunction.CodeFacade = function.CodeFacade;
newFunction.DBSchema = function.Schema;
newFunction.Description = function.Summary;
newFunction.Generated = function.IsGenerated;
newFunction.Name = function.Name;
newFunction.SQL = function.SQL;
newFunction.IsTable = function.IsTable;
newFunction.ReturnVariable = function.ReturnVariable;
newFunction.PrecedenceOrder = function.PrecedenceOrder;
foreach (var field in function.Fields)
{
var newField = root.Database.FunctionColumns.Add();
newField.ResetKey(field.Id.ToString());
newField.ResetId(HashString(newField.Key));
newField.AllowNull = field.Nullable;
newField.CodeFacade = field.CodeFacade;
newField.DataType = (System.Data.SqlDbType)Enum.Parse(typeof(System.Data.SqlDbType), field.DataType.ToString());
newField.Default = field.Default;
newField.Description = field.Summary;
newField.FriendlyName = field.FriendlyName;
newField.Generated = field.IsGenerated;
newField.Length = field.Length;
newField.Name = field.Name;
newField.Scale = field.Scale;
newFunction.Columns.Add(newField.CreateRef(newField.Key));
newField.ParentRef = newFunction.CreateRef(newFunction.Key);
}
//Just in case these are ordered get all sort-ordered parameters first then take on all unordred alphabetized parmameters
var orderedParameters = function.Parameters.Where(x => x.SortOrder > 0).OrderBy(x => x.SortOrder).ToList();
orderedParameters.AddRange(function.Parameters.Where(x => x.SortOrder == 0).OrderBy(x => x.Name).ToList());
foreach (var parameter in orderedParameters)
{
var newParameter = root.Database.FunctionParameters.Add();
newParameter.ResetKey(parameter.Id.ToString());
newParameter.ResetId(HashString(newParameter.Key));
newParameter.AllowNull = parameter.Nullable;
newParameter.CodeFacade = parameter.CodeFacade;
newParameter.DataType = (System.Data.SqlDbType)Enum.Parse(typeof(System.Data.SqlDbType), parameter.DataType.ToString());
newParameter.Default = parameter.Default;
newParameter.Description = parameter.Summary;
newParameter.Generated = parameter.IsGenerated;
newParameter.Length = parameter.Length;
newParameter.Name = parameter.Name;
newParameter.Scale = parameter.Scale;
newParameter.SortOrder = parameter.SortOrder;
var r = newParameter.CreateRef(newParameter.Key);
r.RefType = nHydrate.Generator.Models.ReferenceType.FunctionParameter;
newFunction.Parameters.Add(r);
newParameter.ParentTableRef = newFunction.CreateRef(newFunction.Key);
}
}
#endregion
return root;
}
catch (Exception ex)
{
throw;
}
}