public DbSchemaPackage InspectChanges(DbSchemaPackage existingSchema) { _logger.LogMessage("Starting to Inspect existing schema for changes."); var newSchema = existingSchema.Clone(); foreach (var table in newSchema.Tables) { var newHistoryTable = BuildHistoryTable(table); if (newHistoryTable != null && newHistoryTable.State != DbObjectState.Same) { table.HistoricalTable = newHistoryTable; } } //When history schema doesnt exist, add it if (!newSchema.Schemas.Any(c => c.Name == _historyCommonConfiguration.Schema)) { newSchema.Schemas.Add(new SchemaDetails() { Name = _historyCommonConfiguration.Schema, Schema = "dbo", State = DbObjectState.New }); } _logger.LogMessage("Inspection complete!"); return(newSchema); }
//Hmmm should I pass in DBObjects instead and then use the factory pattern to create a generator per type? public IEnumerable <ChangeScript> Generate(DbSchemaPackage desiredSchema) { _logger.LogMessage("Starting to generate scripts for all changes to the schema."); var tablesWhereHistoryWasModified = desiredSchema.Tables.Where(d => d.HistoricalTable != null && d.HistoricalTable.State != DbObjectState.Same).ToArray(); var creates = tablesWhereHistoryWasModified.Select(c => GenerateCreateTable(c.HistoricalTable)); var triggers = tablesWhereHistoryWasModified.Select(c => GenerateHistoryTrigger(c)); var views = tablesWhereHistoryWasModified.Select(c => GenerateHistoryView(c)); var schemas = desiredSchema.Schemas.Where(s => s.State != DbObjectState.Same).Select(s => GenerateSchemas(s)); return(creates.Union(triggers).Union(views).Union(schemas).Where(s => s != null)); }
public DbSchemaPackage GetDetails(IHistoryTableInspector inspector) { _logger.LogMessage("Starting to read Dac source."); Dictionary <Tuple <string, string>, TableDetails> historyTables = new Dictionary <Tuple <string, string>, TableDetails>(); DbSchemaPackage results = new DbSchemaPackage(); using (TSqlModel model = LoadSqlModel()) { _logger.LogMessage("Inspecting tables"); var allSchemas = model.GetObjects(DacQueryScopes.All, ModelSchema.Schema); foreach (var schema in allSchemas) { results.Schemas.Add(new SchemaDetails() { Name = schema.Name.Parts[0], Schema = "dbo", State = DbObjectState.Same }); } var allTables = model.GetObjects(DacQueryScopes.All, ModelSchema.Table); foreach (var table in allTables) { var currentTable = new TableDetails() { Name = table.Name.Parts[1], Schema = table.Name.Parts[0] }; IEnumerable <TSqlObject> primaryKeys = table.GetReferencing(PrimaryKeyConstraint.Host, DacQueryScopes.UserDefined); foreach (var primaryColumn in primaryKeys) { foreach (var column in primaryColumn.GetReferenced(PrimaryKeyConstraint.Columns)) { currentTable.PrimaryColumnNames.Add(column.Name.Parts[2]); } } foreach (var column in table.GetReferenced(Table.Columns)) { bool isNullable = column.GetProperty <bool>(Column.Nullable); var length = column.GetProperty <int>(Column.Length); var precision = column.GetProperty <int>(Column.Precision); var scale = column.GetProperty <int>(Column.Scale); SqlDataType type = column.GetReferenced(Column.DataType).First().GetProperty <SqlDataType>(DataType.SqlDataType); string sizeDetails = string.Empty; if (length != 0) { sizeDetails = "(" + length + ")"; } else if (type == SqlDataType.DateTime2) { sizeDetails = "(" + scale + ")"; } else if (scale != 0 || precision != 0) { sizeDetails = "(" + precision + "," + scale + ")"; } CommonHistorySqlType commonHistorySqlType; if (_typeMap.TryGetValue(type, out commonHistorySqlType)) { currentTable.Columns.Add(new ColumnDetails() { Name = column.Name.Parts[2], SqlType = _typeMap[type], IsNullable = isNullable, SizeDetails = sizeDetails }); } else { throw new InvalidCastException(string.Format( "Unable to map type {0} for table {1}, and column {1}. This type is not recognized as a history mappable type.", type.ToString(), table.Name, column.Name.Parts[2] )); } } if (inspector.IsHistoryTable(currentTable)) { _logger.LogMessage("History table found: {0}", currentTable.FullName); currentTable.IsHistorical = true; historyTables.Add( new Tuple <string, string>( currentTable.Schema.ToLower(), currentTable.Name.ToLower()), currentTable); } else { results.Tables.Add(currentTable); } } foreach (var table in results.Tables) { var historyNaming = inspector.BuildHistoryName(table); TableDetails matchingHistoryTable = null; if (historyTables.TryGetValue(new Tuple <string, string>(historyNaming.Schema.ToLower(), historyNaming.Name.ToLower()), out matchingHistoryTable)) { _logger.LogMessage("Matched History table {0} to {1}", matchingHistoryTable.FullName, table.FullName); table.HistoricalTable = matchingHistoryTable; } } } return(results); }