protected void AddTableToTableList(string category, SqlTable table, bool hasCategories)
{
if (hasCategories)
{
DocumenterWriter.Write(GetColor(table.SchemaAndTableName), "Tables", category);
}
DocumenterWriter.Write(GetColor(table.SchemaAndTableName), "Tables", table.SchemaAndTableName.Schema);
DocumenterWriter.Write(GetColor(table.SchemaAndTableName), "Tables", table.SchemaAndTableName.TableName);
if (!Customizer.ShouldSkip(table.SchemaAndTableName))
{
DocumenterWriter.WriteLink("Tables", "link", Helper.GetSimplifiedSchemaAndTableName(table.SchemaAndTableName), GetColor(table.SchemaAndTableName));
}
else
{
DocumenterWriter.Write(GetColor(table.SchemaAndTableName), "Tables", "");
}
DocumenterWriter.Write(GetColor(table.SchemaAndTableName), "Tables", table.Columns.Count);
var tableDescription = table.Properties.OfType <SqlTableDescription>().FirstOrDefault();
if (tableDescription != null)
{
DocumenterWriter.Write(GetColor(table.SchemaAndTableName), "Tables", tableDescription.Description);
}
DocumenterWriter.WriteLine("Tables");
}
public void Document(DatabaseDefinition dd)
{
Log(LogSeverity.Information, "Starting on {DatabaseName}.", "SchemaCheckerDocumenter", DatabaseName);
WriteLine("Checks", "Severity", "Type", "Target", "Schema", "Table", "Case", "Description");
var schemaChecker = new SchemaChecker(Context);
var results = schemaChecker.Check(dd);
foreach (var schemaCheck in results.OrderBy(sc => sc.Schema).ThenBy(sc => sc.ElementName).ThenByDescending(sc => sc.Severity))
{
WriteLine("Checks", schemaCheck.Severity, schemaCheck.Type, schemaCheck.TargetType, schemaCheck.Schema, schemaCheck.ElementName, schemaCheck.DisplayName, schemaCheck.DisplayInfo);
}
Log(LogSeverity.Information, "Generating Document content.", "SchemaCheckerDocumenter");
var content = DocumenterWriter.GetContent();
var fileName = FileName ?? (DatabaseName?.Length == 0 ? "Database_checks.xlsx" : DatabaseName + "_checks.xlsx");
var path = Context.DocumenterSettings?.WorkingDirectory;
Log(LogSeverity.Information, "Writing Document file {FileName} to folder {Folder}", "SchemaCheckerDocumenter", fileName, path);
if (!string.IsNullOrEmpty(path))
{
fileName = Path.Combine(path, fileName);
}
File.WriteAllBytes(fileName, content);
}
//private readonly List<KeyValuePair<string, SqlTable>> _sqlTablesByCategoryNew = new List<KeyValuePair<string, SqlTable>>();
//private readonly List<KeyValuePair<string, SqlTable>> _skippedSqlTablesByCategoryNew = new List<KeyValuePair<string, SqlTable>>();
public void Document(DatabaseDefinition originalDd, DatabaseDefinition newDd)
{
Log(LogSeverity.Information, "Starting on {OriginalDatabaseName} vs. {NewDatabaseName}", "ChangeDocumenter", OriginalDatabaseName, NewDatabaseName);
var tablesOriginal = RemoveKnownTechnicalTables(originalDd.GetTables());
foreach (var table in tablesOriginal)
{
if (!Context.CustomizerOriginal.ShouldSkip(table.SchemaAndTableName))
{
_sqlTablesByCategoryOrignal.Add(new KeyValuePair <string, SqlTable>(Context.CustomizerOriginal.Category(table.SchemaAndTableName), table));
}
else
{
_skippedSqlTablesByCategoryOriginal.Add(new KeyValuePair <string, SqlTable>(Context.CustomizerOriginal.Category(table.SchemaAndTableName), table));
}
}
var tablesNew = RemoveKnownTechnicalTables(newDd.GetTables());
foreach (var table in tablesNew)
{
if (!Context.CustomizerNew.ShouldSkip(table.SchemaAndTableName))
{
_sqlTablesByCategoryOrignal.Add(new KeyValuePair <string, SqlTable>(Context.CustomizerNew.Category(table.SchemaAndTableName), table));
}
else
{
_skippedSqlTablesByCategoryOriginal.Add(new KeyValuePair <string, SqlTable>(Context.CustomizerNew.Category(table.SchemaAndTableName), table));
}
}
WriteLine("Database", "", "Original", "New");
WriteLine("Database", "Database name", OriginalDatabaseName, NewDatabaseName);
var noOfTablesOriginal = originalDd.GetTables().Count;
var noOfNotSkippedTablesOriginal = originalDd.GetTables().Count(t => !Context.CustomizerOriginal.ShouldSkip(t.SchemaAndTableName));
var noOfTablesNew = newDd.GetTables().Count;
var noOfNotSkippedTablesNew = newDd.GetTables().Count(t => !Context.CustomizerNew.ShouldSkip(t.SchemaAndTableName));
WriteLine("Database", "Number of documented tables", noOfNotSkippedTablesOriginal, noOfNotSkippedTablesNew);
WriteLine("Database", "Number of skipped tables", noOfTablesOriginal - noOfNotSkippedTablesOriginal, noOfTablesNew - noOfNotSkippedTablesNew);
WriteLine("Database", "Number of tables", noOfTablesOriginal, noOfTablesNew);
var comparer = new Comparer(Context);
var changes = comparer.Compare(originalDd, newDd);
WriteLine("Tables", "Schema", "Table Name", "Event");
foreach (var tableDelete in changes.OfType <TableDelete>())
{
if (!Context.CustomizerOriginal.ShouldSkip(tableDelete.SchemaAndTableName))
{
WriteLine("Tables", tableDelete.SchemaAndTableName.Schema, tableDelete.SchemaAndTableName.TableName, "Deleted");
}
}
foreach (var tableNew in changes.OfType <TableNew>())
{
if (!Context.CustomizerNew.ShouldSkip(tableNew.SchemaAndTableName))
{
WriteLine("Tables", tableNew.SchemaAndTableName.Schema, tableNew.SchemaAndTableName.TableName, "Added");
}
}
var processedTables = new List <SchemaAndTableName>();
foreach (var change in changes.OfType <ColumnMigration>())
{
switch (change)
{
//"Column Name", "Data Type (DbTools)", "Data Type", "Column Length", "Column Scale", "Allow Nulls", "Primary Key", "Identity", "Default Value", "Description", "Foreign Key Name", "Referenced Table", "Link", "Referenced Column"
case ColumnNew column:
{
if (Context.CustomizerNew.ShouldSkip(column.Table.SchemaAndTableName))
{
continue;
}
ProcessColumnMigration(processedTables, column.SqlColumn, "New");
break;
}
case ColumnDelete column:
{
if (Context.CustomizerOriginal.ShouldSkip(column.Table.SchemaAndTableName))
{
continue;
}
ProcessColumnMigration(processedTables, column.SqlColumn, "Delete");
break;
}
case ColumnChange column:
{
if (Context.CustomizerNew.ShouldSkip(column.NewNameAndType.Table.SchemaAndTableName))
{
continue;
}
ProcessColumnMigration(processedTables, column.SqlColumn, "Original");
ProcessColumnMigration(processedTables, column.NewNameAndType, "Changed to");
break;
}
}
}
if (!Context.DocumenterSettings.NoForeignKeys)
{
var processedFKs = new List <SchemaAndTableName>();
foreach (var change in changes.OfType <ForeignKeyMigration>())
{
ProcessTable(processedTables, change.ForeignKey.SqlTable); // Ensure table header
switch (change)
{
case ForeignKeyNew fkNew:
{
if (Context.CustomizerNew.ShouldSkip(fkNew.ForeignKey.ReferredTable.SchemaAndTableName))
{
continue;
}
ProcessForeignKey(processedFKs, fkNew.ForeignKey, "New");
break;
}
case ForeignKeyDelete fkDelete:
{
if (Context.CustomizerOriginal.ShouldSkip(fkDelete.ForeignKey.ReferredTable.SchemaAndTableName))
{
continue;
}
ProcessForeignKey(processedFKs, fkDelete.ForeignKey, "Delete");
break;
}
case ForeignKeyChange fkChange:
{
if (Context.CustomizerNew.ShouldSkip(fkChange.NewForeignKey.ReferredTable.SchemaAndTableName))
{
continue;
}
ProcessForeignKey(processedFKs, fkChange.ForeignKey, "Original");
ProcessForeignKey(processedFKs, fkChange.NewForeignKey, "Change to");
break;
}
}
}
}
if (!Context.DocumenterSettings.NoIndexes)
{
var processedIndexes = new List <SchemaAndTableName>();
foreach (var change in changes.OfType <IndexMigration>())
{
ProcessTable(processedTables, change.Index.SqlTable); // Ensure table header
switch (change)
{
case IndexNew indexNew:
{
if (Context.CustomizerNew.ShouldSkip(indexNew.Index.SqlTable.SchemaAndTableName))
{
continue;
}
ProcessIndex(processedIndexes, indexNew.Index, "New");
break;
}
case IndexDelete indexDelete:
{
if (Context.CustomizerOriginal.ShouldSkip(indexDelete.Index.SqlTable.SchemaAndTableName))
{
continue;
}
ProcessIndex(processedIndexes, indexDelete.Index, "Delete");
break;
}
case IndexChange indexChange:
{
if (Context.CustomizerNew.ShouldSkip(indexChange.NewIndex.SqlTable.SchemaAndTableName))
{
continue;
}
ProcessIndex(processedIndexes, indexChange.Index, "Original");
ProcessIndex(processedIndexes, indexChange.NewIndex, "Change to");
break;
}
}
}
}
if (!Context.DocumenterSettings.NoUniqueConstraints)
{
var processedUniqueConsreaints = new List <SchemaAndTableName>();
foreach (var change in changes.OfType <UniqueConstraintMigration>())
{
ProcessTable(processedTables, change.UniqueConstraint.SqlTable); // Ensure table header
switch (change)
{
case UniqueConstraintNew ucNew:
{
if (Context.CustomizerNew.ShouldSkip(ucNew.UniqueConstraint.SqlTable.SchemaAndTableName))
{
continue;
}
ProcessUniqueConstraint(processedUniqueConsreaints, ucNew.UniqueConstraint, "New");
break;
}
case UniqueConstraintDelete ucDelete:
{
if (Context.CustomizerOriginal.ShouldSkip(ucDelete.UniqueConstraint.SqlTable.SchemaAndTableName))
{
continue;
}
ProcessUniqueConstraint(processedUniqueConsreaints, ucDelete.UniqueConstraint, "Delete");
break;
}
case UniqueConstraintChange ucChange:
{
if (Context.CustomizerNew.ShouldSkip(ucChange.NewUniqueConstraint.SqlTable.SchemaAndTableName))
{
continue;
}
ProcessUniqueConstraint(processedUniqueConsreaints, ucChange.UniqueConstraint, "Original");
ProcessUniqueConstraint(processedUniqueConsreaints, ucChange.NewUniqueConstraint, "Change to");
break;
}
}
}
}
Log(LogSeverity.Information, "Generating Document content.", "ChangeDocumenter");
var content = DocumenterWriter.GetContent();
var fileName = FileName ?? (OriginalDatabaseName == null && NewDatabaseName == null
? "DatabaseChanges.xlsx"
: $"{OriginalDatabaseName}_vs_{NewDatabaseName}.xlsx");
var path = Context.DocumenterSettings?.WorkingDirectory;
Log(LogSeverity.Information, "Writing Document file {FileName} to folder {Folder}", "ChangeDocumenter", fileName, path);
if (!string.IsNullOrEmpty(path))
{
fileName = Path.Combine(path, fileName);
}
File.WriteAllBytes(fileName, content);
}
protected void MergeUpFromPreviousRow(SchemaAndTableName schemaAndTableName, int mergeAmount)
{
DocumenterWriter.MergeUpFromPreviousRow(Helper.GetSimplifiedSchemaAndTableName(schemaAndTableName), mergeAmount);
}
protected void WriteAndMerge(SchemaAndTableName schemaAndTableName, int mergeAmount, params object[] content)
{
DocumenterWriter.WriteAndMerge(GetColor(schemaAndTableName), Helper.GetSimplifiedSchemaAndTableName(schemaAndTableName), mergeAmount, FormatBoolContent(content));
}
protected void WriteLink(SchemaAndTableName schemaAndTableName, string text, SchemaAndTableName targetSchemaAndTableName, Color?backgroundColor = null)
{
DocumenterWriter.WriteLink(Helper.GetSimplifiedSchemaAndTableName(schemaAndTableName), text, Helper.GetSimplifiedSchemaAndTableName(targetSchemaAndTableName), backgroundColor);
}
protected void WriteLine(SchemaAndTableName schemaAndTableName, params object[] content)
{
DocumenterWriter.WriteLine(Helper.GetSimplifiedSchemaAndTableName(schemaAndTableName), FormatBoolContent(content));
}
protected void WriteColor(SchemaAndTableName schemaAndTableName, params object[] content)
{
DocumenterWriter.Write(GetColor(schemaAndTableName), Helper.GetSimplifiedSchemaAndTableName(schemaAndTableName), content);
}
protected void WriteLine(string sheetName, params object[] content)
{
DocumenterWriter.WriteLine(sheetName, FormatBoolContent(content));
}
public void Document(DatabaseDefinition databaseDefinition)
{
Log(LogSeverity.Information, "Starting on {DatabaseName}.", "Documenter", DatabaseName);
var tables = RemoveKnownTechnicalTables(databaseDefinition.GetTables());
foreach (var table in tables)
{
if (!Customizer.ShouldSkip(table.SchemaAndTableName))
{
_sqlTablesByCategory.Add(new KeyValuePair <string, SqlTable>(Customizer.Category(table.SchemaAndTableName), table));
}
else
{
_skippedSqlTablesByCategory.Add(new KeyValuePair <string, SqlTable>(Customizer.Category(table.SchemaAndTableName), table));
}
}
var hasCategories = _sqlTablesByCategory.Any(x => !string.IsNullOrEmpty(x.Key));
var noOfTables = databaseDefinition.GetTables().Count;
var noOfNotSkippedTables = databaseDefinition.GetTables().Count(t => !Customizer.ShouldSkip(t.SchemaAndTableName));
WriteLine("Database", "Database name", DatabaseName);
WriteLine("Database", "Number of documented tables", noOfNotSkippedTables);
WriteLine("Database", "Number of skipped tables", noOfTables - noOfNotSkippedTables);
WriteLine("Database", "Number of tables", noOfTables);
if (hasCategories)
{
WriteLine("Database");
WriteLine("Database", "Documented category", "Table count");
Context.Logger.Log(LogSeverity.Verbose, "Writing tables by category.", "Documenter");
foreach (var category in _sqlTablesByCategory.Select(kvp => kvp.Key).Distinct().OrderBy(x => x))
{
WriteLine("Database", category ?? "(No category)", _sqlTablesByCategory.Count(kvp => kvp.Key == category));
}
if (_skippedSqlTablesByCategory.Count > 0)
{
WriteLine("Database");
WriteLine("Database", "Skipped category", "Table count");
foreach (var category in _skippedSqlTablesByCategory.Select(kvp => kvp.Key).Distinct().OrderBy(x => x))
{
WriteLine("Database", category ?? "(No category)", _skippedSqlTablesByCategory.Count(kvp => kvp.Key == category));
}
}
WriteLine("Tables", "Category", "Schema", "Table Name", "Link", "Number of columns", "Description");
if (!Context.DocumenterSettings.NoInternalDataTypes)
{
WriteLine("All columns", "Category", "Schema", "Table Name", "Column Name", "Data Type (DbTools)", "Data Type", "Column Length", "Column Scale", "Allow Nulls", "Primary Key", "Identity", "Default Value", "Description");
}
else
{
WriteLine("All columns", "Category", "Schema", "Table Name", "Column Name", "Data Type", "Column Length", "Column Scale", "Allow Nulls", "Primary Key", "Identity", "Default Value", "Description");
}
}
else
{
WriteLine("Tables", "Schema", "Table Name", "Link", "Number of columns", "Description");
if (!Context.DocumenterSettings.NoInternalDataTypes)
{
WriteLine("All columns", "Schema", "Table Name", "Column Name", "Data Type (DbTools)", "Data Type", "Column Length", "Column Scale", "Allow Nulls", "Primary Key", "Identity", "Default Value", "Description");
}
else
{
WriteLine("All columns", "Schema", "Table Name", "Column Name", "Data Type", "Column Length", "Column Scale", "Allow Nulls", "Primary Key", "Identity", "Default Value", "Description");
}
}
// Ensure sheet order
if (Customizer is PatternMatchingTableCustomizer)
{
Write("Patt.ma.-tables");
Write("Patt.ma.-patterns");
Write("Patt.ma.-ma.s w exceptions");
Write("Patt.ma.-no matches (unused)");
}
foreach (var tableKvp in _sqlTablesByCategory.OrderBy(kvp => kvp.Key).ThenBy(t => t.Value.SchemaAndTableName.Schema).ThenBy(t => t.Value.SchemaAndTableName.TableName))
{
Context.Logger.Log(LogSeverity.Verbose, "Generating {TableName}.", "Documenter", tableKvp.Value.SchemaAndTableName);
var category = tableKvp.Key;
var table = tableKvp.Value;
AddTableToTableList(category, table, hasCategories);
var sheetColor = GetColor(table.SchemaAndTableName);
if (sheetColor != null)
{
DocumenterWriter.SetSheetColor(Helper.GetSimplifiedSchemaAndTableName(table.SchemaAndTableName), sheetColor.Value);
}
AddTableHeader(hasCategories, category, table);
AddTableDetails(category, table, hasCategories);
}
WriteLine("Tables");
foreach (var tableKvp in _skippedSqlTablesByCategory.OrderBy(kvp => kvp.Key).ThenBy(t => t.Value.SchemaAndTableName.Schema).ThenBy(t => t.Value.SchemaAndTableName.TableName))
{
var category = tableKvp.Key;
var table = tableKvp.Value;
AddTableToTableList(category, table, hasCategories);
}
Context.Logger.Log(LogSeverity.Verbose, "Generating pattern matching info.", "Documenter");
AddPatternMatching();
AddPatternMatchingNoMatch();
Log(LogSeverity.Information, "Generating Document content.", "Documenter");
var content = DocumenterWriter.GetContent();
var fileName = FileName ?? (DatabaseName?.Length == 0 ? "Database.xlsx" : DatabaseName + ".xlsx");
var path = Context.DocumenterSettings?.WorkingDirectory;
Log(LogSeverity.Information, "Writing Document file {FileName} to folder {Folder}", "Documenter", fileName, path);
if (!string.IsNullOrEmpty(path))
{
fileName = Path.Combine(path, fileName);
}
File.WriteAllBytes(fileName, content);
}
protected void AddTableDetails(string category, SqlTable table, bool hasCategories)
{
var pks = table.Properties.OfType <PrimaryKey>().ToList();
foreach (var column in table.Columns)
{
// TODO Create ISqlTypeMapper according to SqlDialect
var sqlType = column.Type;
var columnDocumentInfo = GetColumnDocumentInfo(pks, column);
// TODO internal data types are not OK this way
AddColumnsToTableSheet(column, columnDocumentInfo);
if (hasCategories)
{
if (!Context.DocumenterSettings.NoInternalDataTypes)
{
DocumenterWriter.Write(GetColor(table.SchemaAndTableName), "All columns", category, table.SchemaAndTableName.Schema, table.SchemaAndTableName.TableName, column.Name, sqlType.SqlTypeInfo.SqlDataType, sqlType.SqlTypeInfo.SqlDataType, sqlType.Length, sqlType.Scale, sqlType.IsNullable);
}
else
{
DocumenterWriter.Write(GetColor(table.SchemaAndTableName), "All columns", category, table.SchemaAndTableName.Schema, table.SchemaAndTableName.TableName, column.Name, sqlType, sqlType.Length, sqlType.Scale, sqlType.IsNullable);
}
}
else if (!Context.DocumenterSettings.NoInternalDataTypes)
{
DocumenterWriter.Write(GetColor(table.SchemaAndTableName), "All columns", table.SchemaAndTableName.Schema, table.SchemaAndTableName.TableName, column.Name, sqlType.SqlTypeInfo.SqlDataType, sqlType.SqlTypeInfo.SqlDataType, sqlType.Length, sqlType.Scale, sqlType.IsNullable);
}
else
{
DocumenterWriter.Write(GetColor(table.SchemaAndTableName), "All columns", table.SchemaAndTableName.Schema, table.SchemaAndTableName.TableName, column.Name, sqlType.SqlTypeInfo.SqlDataType, sqlType.Length, sqlType.Scale, sqlType.IsNullable);
}
if (columnDocumentInfo.IsPk)
{
DocumenterWriter.Write(GetColor(table.SchemaAndTableName), "All columns", true);
}
else
{
DocumenterWriter.Write(GetColor(table.SchemaAndTableName), "All columns", "");
}
if (columnDocumentInfo.Identity != null)
{
DocumenterWriter.Write(GetColor(table.SchemaAndTableName), "All columns", $"IDENTITY ({columnDocumentInfo.Identity.Seed.ToString("D", CultureInfo.InvariantCulture)}, {columnDocumentInfo.Identity.Increment.ToString("D", CultureInfo.InvariantCulture)})");
}
else
{
DocumenterWriter.Write(GetColor(table.SchemaAndTableName), "All columns", "");
}
DocumenterWriter.Write(GetColor(table.SchemaAndTableName), "All columns", columnDocumentInfo.DefaultValue);
DocumenterWriter.WriteLine(GetColor(table.SchemaAndTableName), "All columns", columnDocumentInfo.Description);
}
AddForeignKeysToTableSheet(table);
AdIndexesToTableSheet(table);
AddUniqueConstraintsToTableSheet(table);
}