/// <summary>
/// Get matching From ForeignKeyName according to To ForeignKeyName.
/// </summary>
/// <param name="parentType">Parent relationship detail.</param>
/// <param name="toForeignKeyName">To ForeignKeyName.</param>
/// <returns></returns>
private string GetMatchingFromForeignKeyName(RelationshipDetail parentType, string toForeignKeyName)
{
if (parentType == null || string.IsNullOrEmpty(toForeignKeyName))
{
return(string.Empty);
}
for (int i = 0; i < parentType.ToDetails.Keys.Count; i++)
{
var foreignKeyAtIndex = parentType.ToDetails.Keys.ElementAt(i);
if (string.Equals(foreignKeyAtIndex, toForeignKeyName, StringComparison.Ordinal))
{
return(parentType.FromDetails.Keys.ElementAt(i));
}
}
return(string.Empty);
}
private int LoadPostgres(string connectionString, List <SpecialField> auditFields)
{
var tlist = DslPackage.Objects.Postgres.ImportDomain.GetTables(connectionString);
var db = new DataImport.Database();
this.NewDatabase = db;
var allIndexes = DslPackage.Objects.Postgres.ImportDomain.GetIndexes(connectionString);
var allRelations = DslPackage.Objects.Postgres.ImportDomain.GetRelations(connectionString);
foreach (var entity in tlist)
{
//Create table
var newEntity = new DataImport.Entity
{
AllowCreateAudit = entity.Columns.Any(x => x.ColumnName == _model.CreatedByColumnName),
AllowModifyAudit = entity.Columns.Any(x => x.ColumnName == _model.ModifiedByColumnName),
AllowTimestamp = entity.Columns.Any(x => x.ColumnName == _model.ConcurrencyCheckColumnName),
IsTenant = entity.Columns.Any(x => x.ColumnName == _model.TenantColumnName),
Name = entity.TableName,
Schema = entity.SchemaName,
};
db.EntityList.Add(newEntity);
//Load fields
foreach (var column in entity.Columns)
{
var isIndexed = allIndexes.Any(x => x.TableName == newEntity.Name && newEntity.FieldList.Any(z => z.Name == column.ColumnName) && newEntity.FieldList.Count == 1);
newEntity.FieldList.Add(new DataImport.Field
{
DataType = SqlDbType.BigInt,
Identity = column.IsIdentity,
IsIndexed = isIndexed,
Name = column.ColumnName,
Nullable = column.AllowNull,
PrimaryKey = false,
});
}
//TODO: Add multi-column indexes
#region Load relations
foreach (var rowRelationship in allRelations)
{
var constraintName = rowRelationship.IndexName;
var parentTableName = rowRelationship.TableName;
var childTableName = rowRelationship.FKTableName;
var parentTable = db.EntityList.FirstOrDefault(x => x.Name == parentTableName);
var childTable = db.EntityList.FirstOrDefault(x => x.Name == childTableName);
if (parentTable != null && childTable != null)
{
Relationship newRelation = null;
var isAdd = false;
if (db.RelationshipList.Count(x => x.ConstraintName == constraintName) == 0)
{
newRelation = new Relationship();
//if (rowRelationship["object_id"] != System.DBNull.Value)
// newRelation.ImportData = rowRelationship["object_id"].ToString();
newRelation.SourceEntity = parentTable;
newRelation.TargetEntity = childTable;
newRelation.ConstraintName = constraintName;
var search = ("_" + childTable.Name + "_" + parentTable.Name).ToLower();
var roleName = constraintName.ToLower().Replace(search, string.Empty);
if (roleName.Length >= 3)
{
roleName = roleName.Remove(0, 3);
}
var v = roleName.ToLower();
if (v != "fk")
{
newRelation.RoleName = v;
}
isAdd = true;
}
else
{
newRelation = db.RelationshipList.First(x => x.ConstraintName == constraintName);
}
//add the column relationship to the relation
var columnRelationship = new RelationshipDetail();
var parentColumnName = rowRelationship.ColumnName;
var childColumnName = rowRelationship.FKColumnName;
if (parentTable.FieldList.Count(x => x.Name == parentColumnName) == 1 && (childTable.FieldList.Count(x => x.Name == childColumnName) == 1))
{
columnRelationship.ParentField = parentTable.FieldList.First(x => x.Name == parentColumnName);
columnRelationship.ChildField = childTable.FieldList.First(x => x.Name == childColumnName);
newRelation.RelationshipColumnList.Add(columnRelationship);
//ONLY ADD THIS RELATION IF ALL WENT WELL
if (isAdd)
{
parentTable.RelationshipList.Add(newRelation);
}
}
}
}
#endregion
this.NewDatabase.CleanUp();
this.NewDatabase.SetupIdMap(this.CurrentDatabase);
//Load the tree
this.Populate();
}
return(0);
}
public Database Import(string connectionString, IEnumerable <SpecialField> auditFields)
{
try
{
var database = new Database();
#region Load user defined types
LoadUdts(database, connectionString);
#endregion
#region Load Entities
this.ProgressText = "Loading Entities...";
using (var tableReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlDatabaseTables()))
{
while (tableReader.Read())
{
var newEntity = new Entity();
newEntity.Name = tableReader["name"].ToString();
database.EntityList.Add(newEntity);
newEntity.Schema = tableReader["schema"].ToString();
}
}
#endregion
#region Load Entity Fields
using (var columnReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlColumnsForTable()))
{
while (columnReader.Read())
{
var columnName = columnReader["columnName"].ToString();
var tableName = columnReader["tableName"].ToString();
var entity = database.EntityList.FirstOrDefault(x => x.Name == tableName);
//Ensure the field name is not an Audit field
if (entity != null && !auditFields.Any(x => x.Name.Match(columnName)))
{
var maxSortOrder = 0;
if (entity.FieldList.Count > 0)
{
maxSortOrder = entity.FieldList.Max(x => x.SortOrder);
}
var newColumn = new Field()
{
Name = columnName, SortOrder = ++maxSortOrder
};
entity.FieldList.Add(newColumn);
newColumn.Nullable = (int)columnReader["allow_null"] == 1;
if ((int)columnReader["is_identity"] == 1)
{
newColumn.Identity = true;
}
if (columnReader["isPrimaryKey"] != System.DBNull.Value)
{
newColumn.PrimaryKey = true;
}
try
{
newColumn.DataType = DatabaseHelper.GetSQLDataType(columnReader["system_type_id"].ToString(), database.UserDefinedTypes);
}
catch { }
var defaultvalue = columnReader["default_value"].ToString();
SetupDefault(newColumn, defaultvalue);
//newColumn.ImportedDefaultName = "";
newColumn.Length = (int)columnReader["max_length"];
//Decimals are a little different
if (newColumn.DataType == SqlDbType.Decimal)
{
newColumn.Length = (byte)columnReader["precision"];
newColumn.Scale = (int)columnReader["scale"];
}
}
else if (entity != null)
{
if (auditFields.Any(x => (x.Type == SpecialFieldTypeConstants.CreatedDate ||
x.Type == SpecialFieldTypeConstants.CreatedBy) &&
x.Name.ToLower() == columnName.ToLower()))
{
entity.AllowCreateAudit = true;
}
if (auditFields.Any(x => (x.Type == SpecialFieldTypeConstants.ModifiedDate ||
x.Type == SpecialFieldTypeConstants.ModifiedBy) &&
x.Name.ToLower() == columnName.ToLower()))
{
entity.AllowModifyAudit = true;
}
if (auditFields.Any(x => x.Type == SpecialFieldTypeConstants.Timestamp &&
x.Name.ToLower() == columnName.ToLower()))
{
entity.AllowTimestamp = true;
}
if (auditFields.Any(x => x.Type == SpecialFieldTypeConstants.Tenant &&
x.Name.ToLower() == columnName.ToLower()))
{
entity.IsTenant = true;
}
}
}
}
using (var columnReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlColumnsForComputed()))
{
while (columnReader.Read())
{
var tableName = columnReader["tableName"].ToString();
var columnName = columnReader["columnName"].ToString();
var entity = database.EntityList.FirstOrDefault(x => x.Name == tableName);
if (entity != null)
{
var column = entity.FieldList.FirstOrDefault(x => x.Name.ToLower() == columnName.ToLower());
if (column != null)
{
column.IsComputed = true;
column.Formula = columnReader["definition"].ToString();
}
}
}
}
#endregion
#region Load Entity Indexes
using (var indexReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlIndexesForTable()))
{
while (indexReader.Read())
{
var indexName = indexReader["indexname"].ToString();
var columnName = indexReader["columnname"].ToString();
var tableName = indexReader["tableName"].ToString();
var entity = database.EntityList.FirstOrDefault(x => x.Name == tableName);
if (entity != null)
{
var pk = bool.Parse(indexReader["is_primary_key"].ToString());
var column = entity.FieldList.FirstOrDefault(x => x.Name == columnName);
if (column != null && !pk)
{
column.IsIndexed = true;
}
}
}
}
#endregion
#region Load Relations
var dsRelationship = DatabaseHelper.ExecuteDataset(connectionString, SchemaModelHelper.GetSqlForRelationships());
foreach (DataRow rowRelationship in dsRelationship.Tables[0].Rows)
{
var constraintName = rowRelationship["FK_CONSTRAINT_NAME"].ToString();
var parentTableName = (string)rowRelationship["UQ_TABLE_NAME"];
var childTableName = (string)rowRelationship["FK_TABLE_NAME"];
var parentTable = database.EntityList.FirstOrDefault(x => x.Name == parentTableName);
var childTable = database.EntityList.FirstOrDefault(x => x.Name == childTableName);
if (parentTable != null && childTable != null)
{
Relationship newRelation = null;
var isAdd = false;
if (database.RelationshipList.Count(x => x.ConstraintName == constraintName) == 0)
{
newRelation = new Relationship();
if (rowRelationship["object_id"] != System.DBNull.Value)
{
newRelation.ImportData = rowRelationship["object_id"].ToString();
}
newRelation.SourceEntity = parentTable;
newRelation.TargetEntity = childTable;
newRelation.ConstraintName = constraintName;
var search = ("_" + childTable.Name + "_" + parentTable.Name).ToLower();
var roleName = constraintName.ToLower().Replace(search, string.Empty);
if (roleName.Length >= 3)
{
roleName = roleName.Remove(0, 3);
}
var v = roleName.ToLower();
if (v != "fk")
{
newRelation.RoleName = v;
}
isAdd = true;
}
else
{
newRelation = database.RelationshipList.First(x => x.ConstraintName == constraintName);
}
//add the column relationship to the relation
var columnRelationship = new RelationshipDetail();
var parentColumnName = (string)rowRelationship["UQ_COLUMN_NAME"];
var childColumnName = (string)rowRelationship["FK_COLUMN_NAME"];
if (parentTable.FieldList.Count(x => x.Name == parentColumnName) == 1 && (childTable.FieldList.Count(x => x.Name == childColumnName) == 1))
{
columnRelationship.ParentField = parentTable.FieldList.First(x => x.Name == parentColumnName);
columnRelationship.ChildField = childTable.FieldList.First(x => x.Name == childColumnName);
newRelation.RelationshipColumnList.Add(columnRelationship);
//ONLY ADD THIS RELATION IF ALL WENT WELL
if (isAdd)
{
parentTable.RelationshipList.Add(newRelation);
}
}
else
{
System.Diagnostics.Debug.Write(string.Empty);
}
}
}
#endregion
#region Load Views
this.ProgressText = "Loading Views...";
LoadViews(database, connectionString);
#endregion
#region Load Indexes
this.ProgressText = "Loading Indexes...";
LoadIndexes(database, connectionString);
#endregion
LoadUniqueFields(database, connectionString);
return(database);
}
catch (Exception ex /*ignored*/)
{
throw;
}
finally
{
this.ProgressText = string.Empty;
this.ProgressValue = 0;
}
}
/// <summary>
/// Get the the origin class which this foreign key refers to.
/// </summary>
/// <exception cref="ArgumentNullException">
/// When foreignKeyName is null.
/// </exception>
/// <param name="foreignKeyName">Name of foreign key property.</param>
/// <returns>Name of class which this foreign key refers to.</returns>
public string GetOriginOfForeignKey(string foreignKeyName)
{
if (string.IsNullOrEmpty(foreignKeyName))
{
throw new ArgumentNullException(nameof(foreignKeyName));
}
// Try to get from store
Tuple <string, string> key = new Tuple <string, string>(EntityTypeName, foreignKeyName);
if (Store.ForeignKeyOrigin.ContainsKey(key))
{
return(Store.ForeignKeyOrigin[key]);
}
string originOfForeignKey = string.Empty;
// Principal relationship multiplicities
List <RelationshipMultiplicity> principalRelationshipMultiplicity =
new List <RelationshipMultiplicity>()
{
RelationshipMultiplicity.One,
RelationshipMultiplicity.ZeroOrOne
};
// Get the relationship detail which this foreign keys refers.
RelationshipDetail parentType = GetForeignKeyDetails()
.Where(r => r.ToDetails.ContainerClass.Equals(EntityTypeName) &&
r.ToDetails.Keys.Any(k => k.Equals(foreignKeyName)) &&
r.FromDetails != null &&
!string.IsNullOrEmpty(r.FromDetails.ContainerClass) &&
principalRelationshipMultiplicity
.Contains(r.FromDetails.RelationshipMultiplicity))
.FirstOrDefault();
if (parentType != null)
{
var currentReferencedTypeName = parentType.FromDetails.ContainerClass;
var currentForeignKeyName = GetMatchingFromForeignKeyName(parentType, foreignKeyName);
while (!string.IsNullOrEmpty(currentReferencedTypeName))
{
// If EntityTypeName and currentReferencedTypeName is same,
// then this is self reference, so we need to break infinete iteration.
if (EntityTypeName == currentReferencedTypeName)
{
break;
}
IGraphEntityTypeManager referencedTypeManager = ContextFactory
.GetEntityTypeManager(currentReferencedTypeName);
// Get one-to-one principal parent relationship detail,
// because the origin of foreign key is uppermost principal parent.
RelationshipDetail principalParent = referencedTypeManager
.GetForeignKeyDetails()
.Where(r => r.ToDetails.ContainerClass.Equals(currentReferencedTypeName) &&
r.ToDetails.Keys.Contains(currentForeignKeyName) &&
r.FromDetails != null &&
!string.IsNullOrEmpty(r.FromDetails.ContainerClass) &&
principalRelationshipMultiplicity
.Contains(r.FromDetails.RelationshipMultiplicity))
.FirstOrDefault();
if (principalParent != null)
{
// If principal parent is not null set current to this.
currentReferencedTypeName = principalParent.FromDetails.ContainerClass;
currentForeignKeyName = GetMatchingFromForeignKeyName(principalParent, currentForeignKeyName);
}
else
{
// If principal parent is null exit the iteration.
break;
}
}
originOfForeignKey = currentReferencedTypeName;
}
// Add to store
Store.ForeignKeyOrigin.Add(key, originOfForeignKey);
return(originOfForeignKey);
}
public Database Import(string connectionString, IEnumerable<SpecialField> auditFields)
{
try
{
var database = new Database();
database.Collate = DatabaseHelper.GetDatabaseCollation(connectionString);
#region Load user defined types
LoadUdts(database, connectionString);
#endregion
#region Load Entities
this.ProgressText = "Loading Entities...";
using (var tableReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlDatabaseTables()))
{
while (tableReader.Read())
{
var newEntity = new Entity();
newEntity.Name = tableReader["name"].ToString();
database.EntityList.Add(newEntity);
newEntity.Schema = tableReader["schema"].ToString();
}
}
#endregion
#region Load Entity Fields
using (var columnReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlColumnsForTable()))
{
while (columnReader.Read())
{
var columnName = columnReader["columnName"].ToString();
var tableName = columnReader["tableName"].ToString();
var entity = database.EntityList.FirstOrDefault(x => x.Name == tableName);
//Ensure the field name is not an Audit field
if (entity != null && !auditFields.Any(x => x.Name.ToLower() == columnName.ToLower()))
{
var maxSortOrder = 0;
if (entity.FieldList.Count > 0) maxSortOrder = entity.FieldList.Max(x => x.SortOrder);
var newColumn = new Field() { Name = columnName, SortOrder = ++maxSortOrder };
entity.FieldList.Add(newColumn);
newColumn.Nullable = bool.Parse(columnReader["allowNull"].ToString());
if (bool.Parse(columnReader["isIdentity"].ToString()))
newColumn.Identity = true;
if (columnReader["isPrimaryKey"] != System.DBNull.Value)
newColumn.PrimaryKey = true;
try
{
newColumn.DataType = DatabaseHelper.GetSQLDataType(columnReader["xtype"].ToString(), database.UserDefinedTypes);
}
catch { }
var defaultvalue = columnReader["defaultValue"].ToString();
SetupDefault(newColumn, defaultvalue);
//newColumn.ImportedDefaultName = "";
newColumn.Length = (int)columnReader["length"];
//Decimals are a little different
if (newColumn.DataType == SqlDbType.Decimal)
{
newColumn.Length = (byte)columnReader["precision"];
newColumn.Scale = (int)columnReader["scale"];
}
if (columnReader["collation"] != System.DBNull.Value)
{
if (database.Collate != (string)columnReader["collation"])
newColumn.Collate = (string)columnReader["collation"];
}
}
else if (entity != null)
{
if (auditFields.Any(x => (x.Type == SpecialFieldTypeConstants.CreatedDate ||
x.Type == SpecialFieldTypeConstants.CreatedBy) &&
x.Name.ToLower() == columnName.ToLower()))
{
entity.AllowCreateAudit = true;
}
if (auditFields.Any(x => (x.Type == SpecialFieldTypeConstants.ModifedDate ||
x.Type == SpecialFieldTypeConstants.ModifiedBy) &&
x.Name.ToLower() == columnName.ToLower()))
{
entity.AllowModifyAudit = true;
}
if (auditFields.Any(x => x.Type == SpecialFieldTypeConstants.Timestamp &&
x.Name.ToLower() == columnName.ToLower()))
{
entity.AllowTimestamp = true;
}
if (auditFields.Any(x => x.Type == SpecialFieldTypeConstants.Tenant &&
x.Name.ToLower() == columnName.ToLower()))
{
entity.IsTenant = true;
}
}
}
}
using (var columnReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlColumnsForComputed()))
{
while (columnReader.Read())
{
var tableName = columnReader["tableName"].ToString();
var columnName = columnReader["columnName"].ToString();
var entity = database.EntityList.FirstOrDefault(x => x.Name == tableName);
if (entity != null)
{
var column = entity.FieldList.FirstOrDefault(x => x.Name.ToLower() == columnName.ToLower());
if (column != null)
{
column.IsComputed = true;
column.Formula = columnReader["definition"].ToString();
}
}
}
}
#endregion
#region Load Entity Indexes
using (var indexReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlIndexesForTable()))
{
while (indexReader.Read())
{
var indexName = indexReader["indexname"].ToString();
var columnName = indexReader["columnname"].ToString();
var tableName = indexReader["tableName"].ToString();
var entity = database.EntityList.FirstOrDefault(x => x.Name == tableName);
if (entity != null)
{
var pk = bool.Parse(indexReader["is_primary_key"].ToString());
var column = entity.FieldList.FirstOrDefault(x => x.Name == columnName);
if (column != null && !pk)
column.IsIndexed = true;
}
}
}
#endregion
#region Load Relations
var dsRelationship = DatabaseHelper.ExecuteDataset(connectionString, SchemaModelHelper.GetSqlForRelationships());
foreach (DataRow rowRelationship in dsRelationship.Tables[0].Rows)
{
var constraintName = rowRelationship["FK_CONSTRAINT_NAME"].ToString();
var parentTableName = (string)rowRelationship["UQ_TABLE_NAME"];
var childTableName = (string)rowRelationship["FK_TABLE_NAME"];
var parentTable = database.EntityList.FirstOrDefault(x => x.Name == parentTableName);
var childTable = database.EntityList.FirstOrDefault(x => x.Name == childTableName);
if (parentTable != null && childTable != null)
{
Relationship newRelation = null;
var isAdd = false;
if (database.RelationshipList.Count(x => x.ConstraintName == constraintName) == 0)
{
newRelation = new Relationship();
if (rowRelationship["id"] != System.DBNull.Value)
newRelation.ImportData = rowRelationship["id"].ToString();
newRelation.SourceEntity = parentTable;
newRelation.TargetEntity = childTable;
newRelation.ConstraintName = constraintName;
var search = ("_" + childTable.Name + "_" + parentTable.Name).ToLower();
var roleName = constraintName.ToLower().Replace(search, string.Empty);
if (roleName.Length >= 3) roleName = roleName.Remove(0, 3);
var v = roleName.ToLower();
if (v != "fk") newRelation.RoleName = v;
isAdd = true;
}
else
{
newRelation = database.RelationshipList.First(x => x.ConstraintName == constraintName);
}
//add the column relationship to the relation
var columnRelationship = new RelationshipDetail();
var parentColumnName = (string)rowRelationship["UQ_COLUMN_NAME"];
var childColumnName = (string)rowRelationship["FK_COLUMN_NAME"];
if (parentTable.FieldList.Count(x => x.Name == parentColumnName) == 1 && (childTable.FieldList.Count(x => x.Name == childColumnName) == 1))
{
columnRelationship.ParentField = parentTable.FieldList.First(x => x.Name == parentColumnName);
columnRelationship.ChildField = childTable.FieldList.First(x => x.Name == childColumnName);
newRelation.RelationshipColumnList.Add(columnRelationship);
//ONLY ADD THIS RELATION IF ALL WENT WELL
if (isAdd)
parentTable.RelationshipList.Add(newRelation);
}
else
{
System.Diagnostics.Debug.Write(string.Empty);
}
}
}
#endregion
#region Load StoredProcs
LoadStoredProcedures(database, connectionString);
#endregion
#region Load Views
this.ProgressText = "Loading Views...";
LoadViews(database, connectionString);
#endregion
#region Load Functions
this.ProgressText = "Loading Functions...";
LoadFunctions(database, connectionString);
#endregion
#region Load Indexes
this.ProgressText = "Loading Indexes...";
LoadIndexes(database, connectionString);
#endregion
LoadUniqueFields(database, connectionString);
return database;
}
catch (Exception /*ignored*/)
{
throw;
}
finally
{
this.ProgressText = string.Empty;
this.ProgressValue = 0;
}
}
