/// <summary>
/// Second step of the inspection: collect metadata about columns.
/// </summary>
/// <param name="connection">Existing database connection.</param>
/// <param name="tableMeta">The <see cref="TableMeta"/> in which this column belongs to.</param>
private void InspectColumns(DbConnection connection, TableMeta tableMeta)
{
var columns = connection.GetSchema("Columns", new[] { dbName, null, tableMeta.Name });
foreach (DataRow row in columns.Rows)
{
var columnMeta = new ColumnMeta()
{
Table = tableMeta
};
columnMeta.Name = row["COLUMN_NAME"].ToString();
columnMeta.Caption = columnMeta.Name.ToTitleCase();
columnMeta.DataType = MapDbTypeToClrType(row["DATA_TYPE"].ToString());
columnMeta.IsRequired = row["IS_NULLABLE"].ToString() == "NO";
int maxlength = 0;
int.TryParse(row["CHARACTER_MAXIMUM_LENGTH"].ToString(), out maxlength);
if (maxlength > 0)
{
columnMeta.MaxLength = maxlength;
}
columnMeta.OrderNo = int.Parse(row["ORDINAL_POSITION"].ToString());
tableMeta.Columns.Add(columnMeta);
}
}
/// <summary>
/// Inspect primary keys.
/// </summary>
/// <param name="connection">Existing database connection.</param>
/// <param name="tableMeta">The <see cref="TableMeta"/> in which this column belongs to.</param>
private void InspectPrimaryKeys(DbConnection connection, TableMeta tableMeta)
{
var pkColumns = pkInfo.Where(i => i.TableName.Equals(tableMeta.Name, StringComparison.InvariantCultureIgnoreCase) &&
i.SchemaName.Equals(tableMeta.SchemaName, StringComparison.InvariantCultureIgnoreCase));
foreach (var pkColumn in pkColumns)
{
var columnMeta = tableMeta.Columns.Single(c => c.Name.Equals(pkColumn.ColumnName, StringComparison.InvariantCultureIgnoreCase));
if (columnMeta != null)
{
columnMeta.IsPrimaryKey = true;
columnMeta.IsIdentity = pkColumn.IsIdentity;
}
}
}
/// <summary>
/// Inspect foreign keys.
/// </summary>
/// <param name="connection">Existing database connection.</param>
/// <param name="tableMeta">The <see cref="TableMeta"/> in which this column belongs to.</param>
private void InspectForeignKeys(DbConnection connection, TableMeta tableMeta)
{
var foreignKeys = connection.GetSchema("ForeignKeys", new[] { dbName, null, tableMeta.Name });
foreach (DataRow row in foreignKeys.Rows)
{
var constraintName = row[foreignKeys.Columns["CONSTRAINT_NAME"]].ToString();
if (constraintName.Substring(0, 3) == "FK_")
{
var fki = fkInfo.Single(fk => fk.ConstraintName == constraintName);
var foreignKey = fki.ColumnName;
var tableName = fki.TableName;
var refTableName = fki.RefTableName;
var refColName = fki.RefColumnName;
var foreignKeyColumn = tableMeta.Columns.SingleOrDefault(c => c.Name == foreignKey);
if (foreignKeyColumn != null)
{
foreignKeyColumn.IsForeignKey = true;
foreignKeyColumn.ReferenceTable = schemaInfo.Tables.SingleOrDefault(t => t.Name == refTableName);
string fkName = foreignKey.ToTitleCase().Replace(refColName, "").TrimEnd();
var relationName = string.Concat(tableMeta.Name, "_", foreignKeyColumn.ReferenceTable.Name, "_", fkName);
var relationCaption = string.Concat(tableName.ToTitleCase(), " - ", fkName);
foreignKeyColumn.ReferenceTable.Children.Add(
new TableMetaRelation()
{
Parent = foreignKeyColumn.ReferenceTable,
Child = tableMeta,
ForeignKeyName = foreignKeyColumn.Name,
Name = relationName,
Caption = relationCaption.TrimEnd()
}
);
}
}
}
}
/// <summary>
/// Save the inspection result to database.
/// </summary>
/// <param name="sharedContext">Indicates that dbContext used in the process is the shared one.</param>
public void SaveToConfig(bool sharedContext = false)
{
if (string.IsNullOrEmpty(ConfigurationManager.AppSettings["appId"]))
{
throw new ArgumentException("appId must be specified in config.");
}
appId = int.Parse(ConfigurationManager.AppSettings["appId"].ToString());
if (sharedContext && dbConfig == null)
{
throw new ArgumentException("Using shared context requires initialization.");
}
var context = dbConfig;
if (!sharedContext)
{
context = new DotWebDb();
}
EnsureApp(context);
// Save tables
foreach (var tableMeta in SchemaInfo.Tables)
{
Console.WriteLine("Processing table " + tableMeta.Name);
TableMeta dbTable = context.Tables
.Include(t => t.Columns)
.Include(t => t.Columns.Select(c => c.ReferenceTable))
.Include(t => t.Children)
.Include(t => t.Children.Select(c => c.Parent))
.Include(t => t.Children.Select(c => c.Child))
.Include(t => t.App)
.SingleOrDefault(t => t.Name == tableMeta.Name && t.AppId == appId);
var newTable = false;
if (dbTable == null)
{
// Add table
dbTable = tableMeta;
newTable = true;
}
else
{
// Update existing table
dbTable.SchemaName = tableMeta.SchemaName;
}
foreach (var columnMeta in tableMeta.Columns)
{
Console.WriteLine(" Processing column " + columnMeta.Name + " of table " + tableMeta.Name);
ColumnMeta dbColumn = context.Columns.SingleOrDefault(c => c.TableId == dbTable.Id && c.Name == columnMeta.Name);
var newColumn = false;
if (dbColumn == null)
{
// Add columns
dbColumn = columnMeta;
dbColumn.Table = dbTable;
newColumn = true;
}
else
{
// Update existing column
dbColumn.DataType = columnMeta.DataType;
dbColumn.IsForeignKey = columnMeta.IsForeignKey;
dbColumn.IsIdentity = columnMeta.IsIdentity;
dbColumn.IsPrimaryKey = columnMeta.IsPrimaryKey;
dbColumn.IsRequired = columnMeta.IsRequired;
dbColumn.MaxLength = columnMeta.MaxLength;
}
if (columnMeta.ReferenceTable != null && (newColumn || dbColumn.ReferenceTable.Name != columnMeta.ReferenceTable.Name))
{
var dbRefTable = context.Tables.SingleOrDefault(t => tableMeta.AppId == appId &&
t.Name.Equals(columnMeta.ReferenceTable.Name, StringComparison.InvariantCultureIgnoreCase));
if (dbRefTable != null)
{
dbColumn.ReferenceTable = dbRefTable;
}
}
if (newColumn && !newTable)
{
dbTable.Columns.Add(dbColumn);
}
}
// removes columns
var removedColumns = new List <ColumnMeta>();
foreach (var dbColumn in dbTable.Columns)
{
ColumnMeta column = tableMeta.Columns.SingleOrDefault(c => c.Name.Equals(dbColumn.Name, StringComparison.InvariantCultureIgnoreCase));
if (column == null)
{
Console.WriteLine(" Removing column " + dbColumn.Name + " from table " + tableMeta.Name);
// column has been deleted or renamed, so delete related metadata
removedColumns.Add(dbColumn);
}
}
if (removedColumns.Count > 0)
{
context.Columns.RemoveRange(removedColumns);
}
// Processing table meta relations
foreach (var tableMetaRelation in tableMeta.Children)
{
Console.WriteLine(" Processing relation " + tableMetaRelation.Name + " of table " + tableMeta.Name);
TableMetaRelation dbRelation = context.TableRelations.Include(r => r.Child).Include(r => r.Parent)
.SingleOrDefault(r => r.Name.Equals(tableMetaRelation.Name, StringComparison.InvariantCultureIgnoreCase));
if (dbRelation == null)
{
// Add relation
var dbChild = context.Tables.SingleOrDefault(t => t.AppId == appId && t.Name == tableMetaRelation.Child.Name);
if (dbChild != null)
{
tableMetaRelation.Child = dbChild;
}
var dbParent = context.Tables.SingleOrDefault(t => t.AppId == appId && t.Name == tableMetaRelation.Parent.Name);
if (dbParent != null)
{
tableMetaRelation.Parent = dbParent;
}
dbRelation = tableMetaRelation;
context.TableRelations.Add(dbRelation);
}
else
{
// Update existing relation
if (dbRelation.Child.Name != tableMetaRelation.Child.Name)
{
var dbChild = context.Tables.SingleOrDefault(t => t.AppId == appId && t.Name == tableMetaRelation.Child.Name);
if (dbChild != null)
{
dbRelation.Child = dbChild;
}
}
if (dbRelation.Parent.Name != tableMetaRelation.Parent.Name)
{
var dbParent = context.Tables.SingleOrDefault(t => t.AppId == appId && t.Name == tableMetaRelation.Parent.Name);
if (dbParent != null)
{
dbRelation.Parent = dbParent;
}
}
}
}
if (newTable)
{
context.Tables.Add(dbTable);
}
if (context.ChangeTracker.HasChanges())
{
context.SaveChanges();
}
}
// Save navigation
var newGroups = new List <ModuleGroup>();
foreach (var group in schemaInfo.App.Groups)
{
var newModules = new List <Module>();
var dbGroup = context.ModuleGroups.SingleOrDefault(g => g.Name == group.Name && g.AppId == appId);
foreach (var module in group.Modules)
{
var dbModule = context.Modules.Include(m => m.Group).SingleOrDefault(m => m.ModuleType == ModuleType.AutoGenerated &&
m.TableName == module.TableName && m.Group.AppId == appId);
if (dbModule == null)
{
if (dbGroup != null)
{
module.Group = dbGroup;
}
newModules.Add(module);
}
}
if (newModules.Count > 0)
{
context.Modules.AddRange(newModules);
}
}
if (!sharedContext)
{
context.Dispose();
}
}
