/// <summary> /// Analyses the current domain model and database and generates update scripts in an attempt to synchronize them. /// Note that a few places might need manual coding, such as when columns become not nullable, when target types changes, etc. Look for "Warning!!" /// in the generated code /// </summary> internal static string GetUpdateScript <T>(Assembly assembly = null) { var database = Cfg.Databases[assembly ?? typeof(T).Assembly]; var dropPKConstraints = new StringBuilder(); var dropFKConstraints = new StringBuilder(); var dropColumns = new StringBuilder(); var dropTables = new StringBuilder(); var addTables = new StringBuilder(); var addManyToManyTables = new StringBuilder(); var addColumns = new StringBuilder(); var addConstraints = new StringBuilder(); var allCurrentDomainTypes = DwarfHelper.GetValidTypes <T>().ToList(); var currentManyToManyTables = GetCurrentManyToManyTables(allCurrentDomainTypes).Distinct().ToList(); var existingDatabaseTables = DwarfContext <T> .GetConfiguration().Database.ExecuteQuery("SELECT t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id ").Select(x => x.name).ToList(); foreach (var existingTable in existingDatabaseTables.ToArray()) { if (!allCurrentDomainTypes.Select(x => x.Name).Any(x => x.Equals(existingTable)) && !currentManyToManyTables.Any(x => x.Equals(existingTable))) { DropDeadTableConstraints <T>(dropPKConstraints, dropFKConstraints, existingTable); dropTables.AppendLine("IF EXISTS (SELECT * FROM dbo.sysobjects WHERE Id = OBJECT_ID(N'[" + existingTable + "]') AND OBJECTPROPERTY(Id, N'IsUserTable') = 1) DROP Table [" + existingTable + "] "); existingDatabaseTables.Remove(existingTable); var constraints = DwarfContext <T> .GetDatabase().ExecuteCustomQuery <T>("select CONSTRAINT_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '" + existingTable + "' "); foreach (var constraint in constraints) { AddDropConstraint(dropPKConstraints, dropFKConstraints, "IF EXISTS (SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[" + constraint.CONSTRAINT_NAME + "]') AND PARENT_OBJECT_ID = OBJECT_ID('[" + existingTable + "]')) ALTER TABLE [" + existingTable + "] DROP CONSTRAINT [" + constraint.CONSTRAINT_NAME + "]"); } } } foreach (var type in allCurrentDomainTypes) { if (!existingDatabaseTables.Contains(type.Name)) { CreateTable <T>(addTables, type, addConstraints, addManyToManyTables); } foreach (var pi in DwarfHelper.GetManyToManyProperties(type)) { if (!existingDatabaseTables.Contains(ManyToManyAttribute.GetTableName(type, pi.ContainedProperty))) { CreateManyToManyTable(type, pi, addManyToManyTables, addConstraints); } } } foreach (var existingTable in existingDatabaseTables) { if (!allCurrentDomainTypes.Any(x => x.Name.Equals(existingTable))) { continue; } var existingColumns = (List <dynamic>) DwarfContext <T> .GetDatabase().ExecuteCustomQuery <T>("SELECT c.name as name1, t.name as name2, c.max_length, c.is_nullable FROM sys.columns c inner join sys.types t on t.user_type_id = c.user_type_id WHERE object_id = OBJECT_ID('dbo." + existingTable + "') "); var type = allCurrentDomainTypes.First(x => x.Name.Equals(existingTable)); var props = DwarfHelper.GetGemListProperties(type).Union(DwarfHelper.GetDBProperties(type)).ToList(); foreach (var existingColumn in existingColumns) { string columnName = existingColumn.name1.ToString(); var pi = props.FirstOrDefault(x => x.Name.Equals(existingColumn.name2.ToString().Equals("uniqueidentifier") && !x.Name.Equals("Id") ? (columnName.EndsWith("Id") ? columnName.TruncateEnd(2) : columnName) : columnName)); if (pi == null) { dropColumns.AppendLine("IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[" + existingTable + "]') AND Name = '" + columnName + "') ALTER TABLE dbo.[" + existingTable + "] DROP COLUMN " + columnName); if (existingColumn.name2.Equals("uniqueidentifier")) { var fkName = "FK_" + existingTable + "_" + columnName; AddDropConstraint(dropPKConstraints, dropFKConstraints, "IF EXISTS (SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[" + fkName + "]') AND PARENT_OBJECT_ID = OBJECT_ID('[" + existingTable + "]')) ALTER TABLE [" + existingTable + "] DROP CONSTRAINT [" + fkName + "]"); } } } foreach (var pi in props) { if (DwarfPropertyAttribute.GetAttribute(pi.ContainedProperty) == null && !pi.PropertyType.Implements <IGemList>()) { continue; } var existingColumn = existingColumns.FirstOrDefault(x => (pi.PropertyType.Implements <IDwarf>() ? pi.Name + "Id" : pi.Name).Equals(x.name1)); if (existingColumn != null) { var typeChanged = !existingColumn.name2.Equals(TypeToColumnType(pi.ContainedProperty)); var lengthChanged = pi.PropertyType == typeof(string) && (existingColumn.name2.ToString().Equals(DwarfPropertyAttribute.GetAttribute(pi.ContainedProperty).UseMaxLength ? "-1" : "255")); var nullableChanged = (bool.Parse(existingColumn.is_nullable.ToString()) != IsColumnNullable(type, pi.ContainedProperty)); if (typeChanged | nullableChanged | lengthChanged) { addColumns.AppendLine("-- WARNING! TYPE CONVERSION MIGHT FAIL!!! "); addColumns.AppendLine("ALTER TABLE [" + type.Name + "] ALTER COLUMN " + TypeToColumnName(pi) + " " + TypeToColumnConstruction(type, pi.ContainedProperty, true).TruncateEnd(2)); addColumns.AppendLine("GO "); } if (pi.PropertyType.Implements <IDwarf>()) { var fkName = "FK_" + type.Name + "_" + pi.Name + "Id"; var constraintExists = DwarfContext <T> .GetDatabase().ExecuteCustomQuery <T>("SELECT t.name FROM sys.objects obj inner join sys.foreign_key_columns fk on obj.object_id = fk.constraint_object_id inner join sys.columns c on fk.referenced_object_id = c.object_id and fk.referenced_column_id = c.column_id inner join sys.tables t on t.object_id = c.object_id inner join sys.tables t2 on fk.parent_object_id = t2.object_id WHERE obj.type = 'F' and t2.name = '" + type.Name + "' and obj.name = '" + fkName + "' and t.name = '" + pi.PropertyType.Name + "'"); if (!constraintExists.Any()) { dropColumns.AppendLine("IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[" + existingTable + "]') AND Name = '" + existingColumn.name1 + "') ALTER TABLE dbo.[" + existingTable + "] DROP COLUMN " + existingColumn.name1); AddDropConstraint(dropPKConstraints, dropFKConstraints, "IF EXISTS (SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[" + fkName + "]') AND PARENT_OBJECT_ID = OBJECT_ID('[" + existingTable + "]')) ALTER TABLE [" + existingTable + "] DROP CONSTRAINT [" + fkName + "]"); if (IsColumnNullable(type, pi.ContainedProperty)) { addColumns.AppendLine("ALTER TABLE [" + type.Name + "] ADD " + TypeToColumnName(pi) + " " + TypeToColumnConstruction(type, pi.ContainedProperty).TruncateEnd(2)); addColumns.AppendLine("GO "); } else { object value = null; try { value = Activator.CreateInstance(pi.PropertyType); } catch { } addColumns.AppendLine("GO "); addColumns.AppendLine("ALTER TABLE [" + type.Name + "] ADD " + TypeToColumnName(pi) + " " + TypeToColumnType(pi.ContainedProperty)); addColumns.AppendLine("GO "); addColumns.AppendLine("-- WARNING! Value is probably wrong. Correct before you execute! "); addColumns.AppendLine("UPDATE [" + type.Name + "] SET " + TypeToColumnName(pi) + " = " + database.ValueToSqlString(value) + " "); addColumns.AppendLine("GO "); addColumns.AppendLine("ALTER TABLE [" + type.Name + "] ALTER COLUMN " + TypeToColumnName(pi) + " " + TypeToColumnConstruction(type, pi.ContainedProperty, true).TruncateEnd(2)); addColumns.AppendLine("GO "); } var alterTable = "ALTER TABLE [" + type.Name + "] ADD CONSTRAINT [" + fkName + "] FOREIGN KEY (" + pi.Name + "Id) REFERENCES [" + pi.PropertyType.Name + "] (Id)"; if (!DwarfPropertyAttribute.GetAttribute(pi.ContainedProperty).DisableDeleteCascade) { alterTable += " ON DELETE CASCADE "; } addConstraints.AppendLine(alterTable); addConstraints.AppendLine("GO "); } } } else { if (IsColumnNullable(type, pi.ContainedProperty)) { addColumns.AppendLine("ALTER TABLE [" + type.Name + "] ADD " + TypeToColumnName(pi) + " " + TypeToColumnConstruction(type, pi.ContainedProperty).TruncateEnd(2)); addColumns.AppendLine("GO "); } else { object value = null; try { value = Activator.CreateInstance(pi.PropertyType); } catch { } addColumns.AppendLine("GO "); addColumns.AppendLine("ALTER TABLE [" + type.Name + "] ADD " + TypeToColumnName(pi) + " " + " " + TypeToColumnConstruction(type, pi.ContainedProperty).TruncateEnd(2).Replace("NOT NULL", string.Empty)); addColumns.AppendLine("GO "); addColumns.AppendLine("-- WARNING! Value is probably wrong. Correct before you execute! "); addColumns.AppendLine("UPDATE [" + type.Name + "] SET " + TypeToColumnName(pi) + " = " + database.ValueToSqlString(value) + " "); addColumns.AppendLine("GO "); addColumns.AppendLine("ALTER TABLE [" + type.Name + "] ALTER COLUMN " + TypeToColumnName(pi) + " " + TypeToColumnConstruction(type, pi.ContainedProperty, true).TruncateEnd(2)); addColumns.AppendLine("GO "); } if (pi.PropertyType.Implements <IDwarf>()) { var constraintName = "FK_" + type.Name + "_" + pi.Name; var alterTable = "ALTER TABLE [" + type.Name + "] ADD CONSTRAINT [" + constraintName + "Id] FOREIGN KEY (" + pi.Name + "Id) REFERENCES [" + pi.PropertyType.Name + "] (Id)"; if (!DwarfPropertyAttribute.GetAttribute(pi.ContainedProperty).DisableDeleteCascade) { alterTable += " ON DELETE CASCADE "; } addConstraints.AppendLine(alterTable); addColumns.AppendLine("GO "); } } } } foreach (var existingTable in existingDatabaseTables) { var uqConstraints = ((List <dynamic>)DwarfContext <T> .GetDatabase().ExecuteCustomQuery <T>("SELECT obj.name FROM sys.objects obj inner join sys.tables t on obj.parent_object_id = t.object_id WHERE obj.type = 'UQ' and t.name = '" + existingTable + "'")).Select(x => x.name); foreach (var uqConstraint in uqConstraints) { var uqParts = uqConstraint.Split('_'); var type = allCurrentDomainTypes.FirstOrDefault(x => x.Name.Equals(uqParts[1])); if (type != null) { var columns = (List <dynamic>) DwarfContext <T> .GetDatabase().ExecuteCustomQuery <T>("select COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where CONSTRAINT_NAME = '" + uqConstraint + "'"); //Not a unique combination, but a unique column (right?) if (columns.Count == 1) { var pi = ColumnToProperty(type, columns.First().COLUMN_NAME); if (pi != null) { var att = DwarfPropertyAttribute.GetAttribute(pi); if (att != null && !att.IsUnique) { AddDropConstraint(dropPKConstraints, dropFKConstraints, "IF EXISTS (SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[" + uqConstraint + "]') AND PARENT_OBJECT_ID = OBJECT_ID('[" + type.Name + "]')) ALTER TABLE [" + type.Name + "] DROP CONSTRAINT [" + uqConstraint + "]"); } } } else { var uqProperties = (IEnumerable <ExpressionProperty>)DwarfHelper.GetUniqueGroupProperties <T>(type, uqParts[2]); if (!uqProperties.Any()) { AddDropConstraint(dropPKConstraints, dropFKConstraints, "IF EXISTS (SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[" + uqConstraint + "]') AND PARENT_OBJECT_ID = OBJECT_ID('[" + type.Name + "]')) ALTER TABLE [" + type.Name + "] DROP CONSTRAINT [" + uqConstraint + "]"); } else { var difference = uqProperties.Select(x => x.Name).Except(columns.Select(x => x.COLUMN_NAME)); if (difference.Any()) { AddDropConstraint(dropPKConstraints, dropFKConstraints, "IF EXISTS (SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[" + uqConstraint + "]') AND PARENT_OBJECT_ID = OBJECT_ID('[" + type.Name + "]')) ALTER TABLE [" + type.Name + "] DROP CONSTRAINT [" + uqConstraint + "]"); CreateUniqueConstraint <T>(addConstraints, type); } } } } } } foreach (var type in allCurrentDomainTypes) { foreach (var pi in DwarfHelper.GetUniqueDBProperties <T>(type)) { var piName = pi.Name + (pi.PropertyType.Implements <IDwarf>() ? "Id" : string.Empty); var uqName = "UQ_" + type.Name + "_" + piName; var columns = DwarfContext <T> .GetDatabase().ExecuteCustomQuery <T>("select COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where CONSTRAINT_NAME = '" + uqName + "'"); if (columns.Count == 0 && !addColumns.ToString().Contains(uqName) && !addTables.ToString().Contains(uqName)) { addConstraints.AppendLine("ALTER TABLE [" + type.Name + "] ADD CONSTRAINT [" + uqName + "] UNIQUE ([" + pi.Name + "]) "); addConstraints.AppendLine("GO "); } } foreach (var uniqueGroupName in DwarfHelper.GetUniqueGroupNames <T>(type)) { var pis = DwarfHelper.GetUniqueGroupProperties <T>(type, uniqueGroupName); var columns = DwarfContext <T> .GetDatabase().ExecuteCustomQuery <T>("select COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where CONSTRAINT_NAME = 'UQ_" + type.Name + "_" + uniqueGroupName + "'").Select(x => x.COLUMN_NAME).ToList(); if (columns.Any()) { var differnce = pis.Select(x => x.Name).Except(columns); if (differnce.Any()) { AddDropConstraint(dropPKConstraints, dropFKConstraints, "IF EXISTS (SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[" + uniqueGroupName + "]') AND PARENT_OBJECT_ID = OBJECT_ID('[" + type.Name + "]')) ALTER TABLE [" + type.Name + "] DROP CONSTRAINT [" + uniqueGroupName + "]"); CreateUniqueConstraint <T>(addConstraints, type, uniqueGroupName); addConstraints.AppendLine("GO "); } } else { CreateUniqueConstraint <T>(addConstraints, type, uniqueGroupName); addConstraints.AppendLine("GO "); } } var uniqueColumns = DwarfContext <T> .GetDatabase().ExecuteCustomQuery <T>("select COLUMN_NAME, CONSTRAINT_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where CONSTRAINT_NAME like 'UQ_%' and CONSTRAINT_NAME like '%_' + COLUMN_NAME and TABLE_NAME = '" + type.Name + "' "); foreach (var uniqueColumn in uniqueColumns) { var pi = ColumnToProperty(type, uniqueColumn.COLUMN_NAME.ToString()); if (pi == null) { AddDropConstraint(dropPKConstraints, dropFKConstraints, "IF EXISTS (SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[" + uniqueColumn.CONSTRAINT_NAME + "]') AND PARENT_OBJECT_ID = OBJECT_ID('[" + type.Name + "]')) ALTER TABLE [" + type.Name + "] DROP CONSTRAINT [" + uniqueColumn.CONSTRAINT_NAME + "]"); continue; } var att = DwarfPropertyAttribute.GetAttribute(pi); if (att == null) { AddDropConstraint(dropPKConstraints, dropFKConstraints, "IF EXISTS (SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[" + uniqueColumn.CONSTRAINT_NAME + "]') AND PARENT_OBJECT_ID = OBJECT_ID('[" + type.Name + "]')) ALTER TABLE [" + type.Name + "] DROP CONSTRAINT [" + uniqueColumn.CONSTRAINT_NAME + "]"); continue; } if (!att.IsUnique) { AddDropConstraint(dropPKConstraints, dropFKConstraints, "IF EXISTS (SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[" + uniqueColumn.CONSTRAINT_NAME + "]') AND PARENT_OBJECT_ID = OBJECT_ID('[" + type.Name + "]')) ALTER TABLE [" + type.Name + "] DROP CONSTRAINT [" + uniqueColumn.CONSTRAINT_NAME + "]"); } } } var result = AppendSection(dropFKConstraints) + AppendSection(dropPKConstraints) + AppendSection(dropColumns) + AppendSection(dropTables) + AppendSection(addTables) + AppendSection(addManyToManyTables) + AppendSection(addColumns) + AppendSection(addConstraints); if (!string.IsNullOrEmpty(result.Trim())) { return("--WARNING--\r\n" + "--Use these scripts with caution as there's no guarantee that all model changes are --\r\n" + "--reflected here nor that any previously persisted data will remain intact post execution. --\r\n" + "\r\n" + result); } return(string.Empty); }