private bool CheckValueGenerated(CompareLogger2 logger, IProperty property, DatabaseColumn column) { // Leave owned value generated properties to be checked by the owning entity if (property.IsPrimaryKey() && property.DeclaringEntityType.IsOwned()) { return(false); } // Not strictly owned, but acts like owned - Specialized DB Context if (property.IsPrimaryKey() && property.FindFirstPrincipal() != null && property.FindFirstPrincipal().DeclaringEntityType != property.DeclaringEntityType) { return(false); } var colValGen = column.ValueGenerated.ConvertNullableValueGenerated(column.ComputedColumnSql, column.DefaultValueSql); if (colValGen == ValueGenerated.Never.ToString() //There is a case where the property is part of the primary key and the key is not set in the database && property.ValueGenerated == ValueGenerated.OnAdd && property.IsKey() //We assume that a integer of some form should be provided by the database && !IntegerTypes.Contains(property.ClrType)) { return(false); } return(logger.CheckDifferent(property.ValueGenerated.ToString(), colValGen, CompareAttributes.ValueGenerated, _caseComparison)); }
private void LookForUnusedColumns(IReadOnlyList <CompareLog> firstStageLogs, CompareLog log) { var logger = new CompareLogger2(CompareType.Column, null, _logs, _ignoreList, () => _hasErrors = true); var tableDict = _databaseModel.Tables.ToDictionary(x => x.FormSchemaTableFromDatabase(_databaseModel.DefaultSchema), _caseComparer); //because of table splitting and TPH we need to groups properties by table name to correctly say what columns are missed var entityColsGrouped = firstStageLogs.SelectMany(p => p.SubLogs) .Where(x => x.State == CompareState.Ok && x.Type == CompareType.Entity) .GroupBy(x => x.Expected, y => y.SubLogs .Where(x => x.State == CompareState.Ok && x.Type == CompareType.Property) .Select(p => p.Expected)); var entityColsByTableDict = entityColsGrouped.ToDictionary(x => x.Key, y => y.SelectMany(x => x.ToList()), _caseComparer); foreach (var entityLog in firstStageLogs.SelectMany(p => p.SubLogs) .Where(x => x.State == CompareState.Ok && x.Type == CompareType.Entity)) { if (tableDict.ContainsKey(entityLog.Expected)) { var dbColNames = tableDict[entityLog.Expected].Columns.Select(x => x.Name); var colsNotUsed = dbColNames.Where(p => !entityColsByTableDict[entityLog.Expected].Contains(p, _caseComparer)); foreach (var colName in colsNotUsed) { logger.ExtraInDatabase(colName, CompareAttributes.ColumnName, entityLog.Expected); } } } }
private bool ComparePropertyToColumn(IColumnBase relColumn, CompareLogger2 logger, IProperty property, DatabaseColumn column, bool isView) { var error = logger.CheckDifferent(property.GetColumnType(), column.StoreType, CompareAttributes.ColumnType, _caseComparison); error |= logger.CheckDifferent(relColumn.IsNullable.NullableAsString(), column.IsNullable.NullableAsString(), CompareAttributes.Nullability, _caseComparison); error |= logger.CheckDifferent(property.GetComputedColumnSql().RemoveUnnecessaryBrackets(), column.ComputedColumnSql.RemoveUnnecessaryBrackets(), CompareAttributes.ComputedColumnSql, _caseComparison); if (property.GetComputedColumnSql() != null) { error |= logger.CheckDifferent(property.GetIsStored()?.ToString() ?? false.ToString() , column.IsStored.ToString(), CompareAttributes.PersistentComputedColumn, _caseComparison); } var defaultValue = property.TryGetDefaultValue(out var propDefaultValue) ? _relationalTypeMapping.FindMapping(propDefaultValue.GetType()) .GenerateSqlLiteral(propDefaultValue) : property.GetDefaultValueSql().RemoveUnnecessaryBrackets(); error |= logger.CheckDifferent(defaultValue, column.DefaultValueSql.RemoveUnnecessaryBrackets(), CompareAttributes.DefaultValueSql, _caseComparison); if (!isView) { error |= CheckValueGenerated(logger, property, column); } return(error); }
public bool CompareLogsToDatabase(IReadOnlyList <CompareLog> firstStageLogs) { var logger = new CompareLogger2(CompareType.Database, _databaseModel.DatabaseName, _logs, _ignoreList, () => _hasErrors = true); logger.MarkAsOk(null); LookForUnusedTables(firstStageLogs, _logs.Last()); LookForUnusedColumns(firstStageLogs, _logs.Last()); LookForUnusedIndexes(firstStageLogs, _logs.Last()); return(_hasErrors); }
private void LookForUnusedTables(IReadOnlyList <CompareLog> firstStageLogs, CompareLog log) { var logger = new CompareLogger2(CompareType.Table, null, log.SubLogs, _ignoreList, () => _hasErrors = true); var databaseTableNames = _databaseModel.Tables.Select(x => x.FormSchemaTableFromDatabase(_databaseModel.DefaultSchema)); var allEntityTableNames = firstStageLogs.SelectMany(p => p.SubLogs) .Where(x => x.State == CompareState.Ok && x.Type == CompareType.Entity) .Select(p => p.Expected).OrderBy(p => p).Distinct().ToList(); var tablesNotUsed = databaseTableNames.Where(p => !allEntityTableNames.Contains(p, _caseComparer)); foreach (var tableName in tablesNotUsed) { logger.ExtraInDatabase(null, CompareAttributes.NotSet, tableName); } }
private bool CheckValueGenerated(CompareLogger2 logger, IProperty property, DatabaseColumn column) { var colValGen = column.ValueGenerated.ConvertNullableValueGenerated(column.ComputedColumnSql, column.DefaultValueSql); if (colValGen == ValueGenerated.Never.ToString() //There is a case where the property is part of the primary key and the key is not set in the database && property.ValueGenerated == ValueGenerated.OnAdd && property.IsKey() //We assume that a integer of some form should be provided by the database && !IntegerTypes.Contains(property.ClrType)) { return(false); } return(logger.CheckDifferent(property.ValueGenerated.ToString(), colValGen, CompareAttributes.ValueGenerated, _caseComparison)); }
public bool CompareModelToDatabase(DatabaseModel databaseModel) { _defaultSchema = databaseModel.DefaultSchema; var dbLogger = new CompareLogger2(CompareType.DbContext, _dbContextName, _logs, _ignoreList, () => _hasErrors = true); //Check things about the database, such as sequences dbLogger.MarkAsOk(_dbContextName); CheckDatabaseOk(_logs.Last(), _model, databaseModel); _tableViewDict = databaseModel.Tables.ToDictionary(x => x.FormSchemaTableFromDatabase(_defaultSchema), _caseComparer); var entitiesNotMappedToTableOrView = _model.GetEntityTypes().Where(x => x.FormSchemaTableFromModel() == null).ToList(); if (entitiesNotMappedToTableOrView.Any()) { dbLogger.MarkAsNotChecked(null, string.Join(", ", entitiesNotMappedToTableOrView.Select(x => x.ClrType.Name)), CompareAttributes.NotMappedToDatabase); } foreach (var entityType in _model.GetEntityTypes().Where(x => !entitiesNotMappedToTableOrView.Contains(x))) { var logger = new CompareLogger2(CompareType.Entity, entityType.ClrType.Name, _logs.Last().SubLogs, _ignoreList, () => _hasErrors = true); if (_tableViewDict.ContainsKey(entityType.FormSchemaTableFromModel())) { var databaseTable = _tableViewDict[entityType.FormSchemaTableFromModel()]; //Checks for table matching var log = logger.MarkAsOk(entityType.FormSchemaTableFromModel()); if (entityType.GetTableName() != null) { //Its not a view logger.CheckDifferent(entityType.FindPrimaryKey()?.GetName() ?? NoPrimaryKey, databaseTable.PrimaryKey?.Name ?? NoPrimaryKey, CompareAttributes.ConstraintName, _caseComparison); } CompareColumns(log, entityType, databaseTable); CompareForeignKeys(log, entityType, databaseTable); CompareIndexes(log, entityType, databaseTable); } else { logger.NotInDatabase(entityType.FormSchemaTableFromModel(), CompareAttributes.TableName); } } return(_hasErrors); }
private void CompareForeignKeys(CompareLog log, IEntityType entityType, DatabaseTable table) { var fKeyDict = table.ForeignKeys.ToDictionary(x => x.Name, _caseComparer); foreach (var entityFKey in entityType.GetForeignKeys()) { var entityFKeyProps = entityFKey.Properties; var constraintName = entityFKey.GetConstraintName(); var logger = new CompareLogger2(CompareType.ForeignKey, constraintName, log.SubLogs, _ignoreList, () => _hasErrors = true); if (IgnoreForeignKeyIfInSameTableOrTpT(entityType, entityFKey, table)) { continue; } if (fKeyDict.ContainsKey(constraintName)) { //Now check every foreign key var error = false; var thisKeyCols = fKeyDict[constraintName].Columns.ToDictionary(x => x.Name, _caseComparer); foreach (var fKeyProp in entityFKeyProps) { var columnName = GetColumnNameTakingIntoAccountSchema(fKeyProp, table); if (!thisKeyCols.ContainsKey(columnName)) { logger.NotInDatabase(columnName); error = true; } } error |= logger.CheckDifferent(entityFKey.DeleteBehavior.ToString(), fKeyDict[constraintName].OnDelete.ConvertReferentialActionToDeleteBehavior(entityFKey.DeleteBehavior), CompareAttributes.DeleteBehavior, _caseComparison); if (!error) { logger.MarkAsOk(constraintName); } } else { logger.NotInDatabase(constraintName, CompareAttributes.ConstraintName); } } }
private void LookForUnusedIndexes(IReadOnlyList <CompareLog> firstStageLogs, CompareLog log) { var logger = new CompareLogger2(CompareType.Index, null, _logs, _ignoreList, () => _hasErrors = true); var tableDict = _databaseModel.Tables.ToDictionary(x => x.FormSchemaTableFromDatabase(_databaseModel.DefaultSchema), _caseComparer); foreach (var entityLog in firstStageLogs.SelectMany(p => p.SubLogs) .Where(x => x.State == CompareState.Ok && x.Type == CompareType.Entity)) { if (tableDict.ContainsKey(entityLog.Expected)) { var indexCol = tableDict[entityLog.Expected].Indexes.Select(x => x.Name); var allEfIndexNames = entityLog.SubLogs .Where(x => x.State == CompareState.Ok && x.Type == CompareType.Index) .Select(p => p.Expected).OrderBy(p => p).Distinct().ToList(); var indexesNotUsed = indexCol.Where(p => !allEfIndexNames.Contains(p, _caseComparer)); foreach (var indexName in indexesNotUsed) { logger.ExtraInDatabase(indexName, CompareAttributes.IndexConstraintName, entityLog.Expected); } } } }
private void CompareIndexes(CompareLog log, IEntityType entityType, DatabaseTable table) { var indexDict = DatabaseIndexData.GetIndexesAndUniqueConstraints(table).ToDictionary(x => x.Name, _caseComparer); foreach (var entityIdx in entityType.GetIndexes()) { var entityIdxprops = entityIdx.Properties; var allColumnNames = string.Join(",", entityIdxprops .Select(x => GetColumnNameTakingIntoAccountSchema(x, table))); var logger = new CompareLogger2(CompareType.Index, allColumnNames, log.SubLogs, _ignoreList, () => _hasErrors = true); var constraintName = entityIdx.GetDatabaseName(); if (indexDict.ContainsKey(constraintName)) { //Now check every column in an index var error = false; var thisIdxCols = indexDict[constraintName].Columns.ToDictionary(x => x.Name, _caseComparer); foreach (var idxProp in entityIdxprops) { var columnName = GetColumnNameTakingIntoAccountSchema(idxProp, table); if (!thisIdxCols.ContainsKey(columnName)) { logger.NotInDatabase(columnName); error = true; } } error |= logger.CheckDifferent(entityIdx.IsUnique.ToString(), indexDict[constraintName].IsUnique.ToString(), CompareAttributes.Unique, _caseComparison); if (!error) { logger.MarkAsOk(constraintName); } } else { logger.NotInDatabase(constraintName, CompareAttributes.IndexConstraintName); } } }
private void CompareColumns(CompareLog log, IEntityType entityType, DatabaseTable table) { var isView = entityType.GetTableName() == null; var primaryKeyDict = table.PrimaryKey?.Columns.ToDictionary(x => x.Name, _caseComparer) ?? new Dictionary <string, DatabaseColumn>(); var efPKeyConstraintName = isView ? NoPrimaryKey : entityType.FindPrimaryKey()?.GetName() ?? NoPrimaryKey; bool pKeyError = false; var pKeyLogger = new CompareLogger2(CompareType.PrimaryKey, efPKeyConstraintName, log.SubLogs, _ignoreList, () => { pKeyError = true; //extra set of pKeyError _hasErrors = true; }); if (!isView) { pKeyLogger.CheckDifferent(efPKeyConstraintName, table.PrimaryKey?.Name ?? NoPrimaryKey, CompareAttributes.ConstraintName, _caseComparison); } var columnDict = table.Columns.ToDictionary(x => x.Name, _caseComparer); // SQL Server only feature. Will not affect other databases var temporalColumnIgnores = table.GetAnnotations() #pragma warning disable EF1001 // Internal EF Core API usage. .Where(a => a.Name == SqlServerAnnotationNames.TemporalPeriodStartPropertyName || a.Name == SqlServerAnnotationNames.TemporalPeriodEndPropertyName) #pragma warning restore EF1001 // Internal EF Core API usage. .Select(a => (string)a.Value) .ToArray(); //This finds all the Owned Types and THP foreach (var property in entityType.GetProperties()) { // Ignore temporal shadow properties (SQL Server) if (property.IsShadowProperty() && temporalColumnIgnores.Contains(property.Name)) { continue; } var colLogger = new CompareLogger2(CompareType.Property, property.Name, log.SubLogs, _ignoreList, () => _hasErrors = true); var columnName = GetColumnNameTakingIntoAccountSchema(property, table, isView); if (columnName == null) { //This catches properties in TPH, split tables, and Owned Types where the properties are not mapped to the current table continue; } if (columnDict.ContainsKey(columnName)) { var reColumn = GetRelationalColumn(columnName, table, isView); var error = ComparePropertyToColumn(reColumn, colLogger, property, columnDict[columnName], isView); //check for primary key if (property.IsPrimaryKey() && //This remove TPH, Owned Types primary key checks !isView != primaryKeyDict.ContainsKey(columnName)) { if (!primaryKeyDict.ContainsKey(columnName)) { pKeyLogger.NotInDatabase(columnName, CompareAttributes.ColumnName); error = true; } else { pKeyLogger.ExtraInDatabase(columnName, CompareAttributes.ColumnName, table.PrimaryKey.Name); } } if (!error) { //There were no errors noted, so we mark it as OK colLogger.MarkAsOk(columnName); } } else { colLogger.NotInDatabase(GetColumnNameTakingIntoAccountSchema(property, table), CompareAttributes.ColumnName); } } if (!pKeyError) { pKeyLogger.MarkAsOk(efPKeyConstraintName); } }
private void CompareColumns(CompareLog log, IEntityType entityType, DatabaseTable table) { var isView = entityType.GetTableName() == null; var primaryKeyDict = table.PrimaryKey?.Columns.ToDictionary(x => x.Name, _caseComparer) ?? new Dictionary <string, DatabaseColumn>(); var efPKeyConstraintName = isView ? NoPrimaryKey : entityType.FindPrimaryKey()?.GetName() ?? NoPrimaryKey; bool pKeyError = false; var pKeyLogger = new CompareLogger2(CompareType.PrimaryKey, efPKeyConstraintName, log.SubLogs, _ignoreList, () => { pKeyError = true; //extra set of pKeyError _hasErrors = true; }); if (!isView) { pKeyLogger.CheckDifferent(efPKeyConstraintName, table.PrimaryKey?.Name ?? NoPrimaryKey, CompareAttributes.ConstraintName, _caseComparison); } var columnDict = table.Columns.ToDictionary(x => x.Name, _caseComparer); //This finds all the Owned Types and THP foreach (var property in entityType.GetProperties()) { var colLogger = new CompareLogger2(CompareType.Property, property.Name, log.SubLogs, _ignoreList, () => _hasErrors = true); var columnName = GetColumnNameTakingIntoAccountSchema(property, table, isView); if (columnName == null) { //This catches properties in TPH, split tables, and Owned Types where the properties are not mapped to the current table continue; } if (columnDict.ContainsKey(columnName)) { var reColumn = GetRelationalColumn(columnName, table, isView); var error = ComparePropertyToColumn(reColumn, colLogger, property, columnDict[columnName], isView); //check for primary key if (property.IsPrimaryKey() && //This remove TPH, Owned Types primary key checks !isView != primaryKeyDict.ContainsKey(columnName)) { if (!primaryKeyDict.ContainsKey(columnName)) { pKeyLogger.NotInDatabase(columnName, CompareAttributes.ColumnName); error = true; } else { pKeyLogger.ExtraInDatabase(columnName, CompareAttributes.ColumnName, table.PrimaryKey.Name); } } if (!error) { //There were no errors noted, so we mark it as OK colLogger.MarkAsOk(columnName); } } else { colLogger.NotInDatabase(GetColumnNameTakingIntoAccountSchema(property, table), CompareAttributes.ColumnName); } } if (!pKeyError) { pKeyLogger.MarkAsOk(efPKeyConstraintName); } }