/// <summary> /// Returns the current domain model as "insert into" from another database with the same structure. /// The tables will be unordered (add your constraints afterwards) and remember to tweak each table as needed /// </summary> internal static string GetTransferScript <T>(string otherDatabaseName, params ExpressionProperty[] toSkip) { var tables = new StringBuilder(); var manyToManyTables = new StringBuilder(); foreach (var type in DwarfHelper.GetValidTypes <T>()) { var typeProps = (DwarfHelper.GetDBProperties(type).Union(DwarfHelper.GetGemListProperties(type))).Where(x => !toSkip.Contains(x)).Flatten(x => "[" + (x.PropertyType.Implements <IDwarf>() ? x.Name + "Id" : x.Name) + "], "); typeProps = typeProps.TruncateEnd(2); tables.AppendLine(string.Format("INSERT INTO [{0}] ({1}) (SELECT {1} from [{2}].dbo.[{0}])", type.Name, typeProps, otherDatabaseName)); foreach (var ep in DwarfHelper.GetManyToManyProperties(type)) { var tableName = ManyToManyAttribute.GetTableName(type, ep.ContainedProperty); if (manyToManyTables.ToString().Contains(tableName)) { continue; } var m2mProps = "[" + type.Name + "Id], [" + ep.PropertyType.GetGenericArguments()[0].Name + "Id]"; manyToManyTables.AppendLine(string.Format("INSERT INTO [{0}] ({1}) (SELECT {1} from [{2}].dbo.[{0}])", tableName, m2mProps, otherDatabaseName)); } } return(tables.ToString() + manyToManyTables); }
/// <summary> /// Generates create table commands for the supplied type /// </summary> internal static void CreateTable <T>(StringBuilder tables, Type type, StringBuilder constraints, StringBuilder manyToManyTables) { tables.AppendLine("CREATE TABLE [" + type.Name + "] ( "); foreach (var pi in DwarfHelper.GetDBProperties(type)) { tables.AppendLine(TypeToColumnName(pi) + " " + TypeToColumnConstruction(type, pi.ContainedProperty)); } foreach (var pi in DwarfHelper.GetGemListProperties(type)) { tables.AppendLine(TypeToColumnName(pi) + " " + TypeToColumnConstruction(type, pi.ContainedProperty)); } foreach (var manyToManyProperty in DwarfHelper.GetManyToManyProperties(type)) { CreateManyToManyTable(type, manyToManyProperty, manyToManyTables, constraints); } var keys = String.Empty; foreach (var propertyInfo in DwarfHelper.GetPKProperties(type)) { keys += TypeToColumnName(propertyInfo) + ", "; } if (!string.IsNullOrEmpty(keys)) { keys = keys.TruncateEnd(2); keys = "CONSTRAINT [PK_" + type.Name + "] PRIMARY KEY (" + keys + ")"; tables.AppendLine(keys); } foreach (var pi in DwarfHelper.GetFKProperties <T>(type)) { 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 "; } constraints.AppendLine(alterTable); } CreateUniqueConstraint <T>(constraints, type); tables.AppendLine(") "); tables.AppendLine(); }
/// <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); }
/// <summary> /// Bulk inserts the supplied objects using SqlBulkCopy /// </summary> public virtual void BulkInsert <T>(IEnumerable <T> objects) where T : Dwarf <T>, new() { var list = objects.ToList(); if (!list.Any()) { return; } var properties = DwarfHelper.GetDBProperties <T>().ToList(); var dt = new DataTable(); foreach (var pi in properties) { if (pi.PropertyType.Implements <IDwarf>()) { dt.Columns.Add(pi.Name + "Id", typeof(Guid)); } else if (pi.PropertyType.IsEnum()) { dt.Columns.Add(pi.Name, typeof(string)); } else { dt.Columns.Add(pi.Name, Nullable.GetUnderlyingType(pi.PropertyType) ?? pi.PropertyType); } } Parallel.ForEach(list, obj => { if (obj.IsSaved) { return; } obj.Id = obj.Id.HasValue ? obj.Id.Value : Guid.NewGuid(); var row = new object[properties.Count]; for (var i = 0; i < properties.Count; i++) { var value = properties[i].GetValue(obj); if (properties[i].PropertyType.Implements <IDwarf>()) { if (value != null) { row[i] = ((IDwarf)value).Id; } else { row[i] = DBNull.Value; } } else { row[i] = value ?? DBNull.Value; } } obj.IsSaved = true; lock (dt.Rows.SyncRoot) dt.Rows.Add(row); }); var con = (SqlConnection)DbContextHelper <T> .OpenConnection(); var copy = new SqlBulkCopy(con, SqlBulkCopyOptions.Default, (SqlTransaction)DbContextHelper <T> .Transaction) { DestinationTableName = typeof(T).Name, BulkCopyTimeout = 10000, BatchSize = 2500, }; foreach (DataColumn column in dt.Columns) { copy.ColumnMappings.Add(column.ColumnName, column.ColumnName); } copy.WriteToServer(dt); if (con != DbContextHelper <T> .Connection) { con.Dispose(); } objects.ForEachX(x => x.IsSaved = true); }