/// <summary> /// Generates create table commands for the supplied type /// </summary> internal static void CreateManyToManyTable(Type type, ExpressionProperty pi, StringBuilder manyToManyTables, StringBuilder foreignKeys) { var nameOwner = type.Name; var nameChild = pi.PropertyType.GetGenericArguments()[0].Name; var tableName = ManyToManyAttribute.GetTableName(type, pi.ContainedProperty); if (manyToManyTables.ToString().Contains("CREATE TABLE [" + tableName + "] ( ")) { return; } manyToManyTables.AppendLine("CREATE TABLE [" + tableName + "] ( "); manyToManyTables.AppendLine("[" + nameOwner + "Id] [uniqueidentifier] NOT NULL, "); manyToManyTables.AppendLine("[" + nameChild + "Id] [uniqueidentifier] NOT NULL, "); manyToManyTables.AppendLine("CONSTRAINT [PK_" + tableName + "] PRIMARY KEY (" + nameOwner + "Id, " + nameChild + "Id)"); manyToManyTables.AppendLine(")"); manyToManyTables.AppendLine(); var ownerConstraintName = "FK_" + tableName + "_" + nameOwner; var childConstraintName = "FK_" + tableName + "_" + nameChild; foreignKeys.AppendLine("ALTER TABLE [" + tableName + "] ADD CONSTRAINT [" + ownerConstraintName + "Id] FOREIGN KEY (" + nameOwner + "Id) REFERENCES [" + nameOwner + "] (Id) ON DELETE CASCADE "); foreignKeys.AppendLine("ALTER TABLE [" + tableName + "] ADD CONSTRAINT [" + childConstraintName + "Id] FOREIGN KEY (" + nameChild + "Id) REFERENCES [" + nameChild + "] (Id) ON DELETE CASCADE "); }
/// <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> ///Inserts two foreign key refernces into a mapping table /// </summary> public virtual void InsertManyToMany <T>(IDwarf obj1, IDwarf obj2, string alternateTableName = null) { if (!obj1.IsSaved) { obj1.Save(); } if (!obj2.IsSaved) { obj2.Save(); } var type1 = obj1.GetType(); var type2 = obj2.GetType(); var tableName = ManyToManyAttribute.GetManyToManyTableName(type1, type2, alternateTableName); DbContextHelper <T> .ClearCacheForType(type1); DbContextHelper <T> .ClearCacheForType(type2); var preReq = string.Format("IF NOT EXISTS (SELECT * FROM [{0}] WHERE {1}Id = {2} AND {3}Id = {4}) \r\n", tableName, type1.Name, ValueToSqlString(obj1.Id), type2.Name, ValueToSqlString(obj2.Id)); var query = new QueryBuilder <T>() .InsertInto(tableName) .Values(type1.Name + "Id", obj1.Id) .Values(type2.Name + "Id", obj2.Id) .ToQuery(); ExecuteNonQuery <T>(preReq + query); }
public List <Tuple <string, Object> > GetColumnsAndValues(Object instance, bool isInherited = false) { List <Tuple <string, Object> > list = new List <Tuple <string, Object> > { }; Type instanceType = instance.GetType(); PropertyInfo[] properties; if (isInherited) { properties = GetTypeAllProperties(instanceType); } else { properties = GetTypeProperties(instanceType); } foreach (PropertyInfo property in properties) { MethodInfo strGetter = property.GetGetMethod(nonPublic: true); var value = strGetter.Invoke(instance, null); string columnName; ColumnAttribute columnAttribute = (ColumnAttribute)property.GetCustomAttribute(typeof(ColumnAttribute), false); if (columnAttribute == null) { continue; } if (columnAttribute._columnName == null) { columnName = property.Name; } else { columnName = columnAttribute._columnName; } OneToOneAttribute oneToOneAttribute = (OneToOneAttribute)property.GetCustomAttribute(typeof(OneToOneAttribute), false); OneToManyAttribute oneToManyAttribute = (OneToManyAttribute)property.GetCustomAttribute(typeof(OneToManyAttribute), false); ManyToManyAttribute manyToManyAttribute = (ManyToManyAttribute)property.GetCustomAttribute(typeof(ManyToManyAttribute), false); if (oneToOneAttribute == null && oneToManyAttribute == null && manyToManyAttribute == null) { list.Add(new Tuple <string, Object>(columnName, value)); } } return(list); }
/// <summary> ///Inserts two foreign key refernces into a mapping tables /// </summary> public virtual List <T> SelectManyToMany <T>(IDwarf owner, string alternateTableName = null) where T : Dwarf <T>, new() { if (owner == null || !owner.IsSaved) { return(new List <T>()); } var targetType = typeof(T); var ownerType = owner.GetType(); var tableName = ManyToManyAttribute.GetManyToManyTableName(targetType, ownerType, alternateTableName); var command = new QueryBuilderLight().Select <T>() .From <T>() .InnerJoin("[" + tableName + "] ON [" + tableName + "].[" + targetType.Name + "Id] = [" + targetType.Name + "].[Id]") .Where("[" + tableName + "].[" + ownerType.Name + "Id]", owner.Id, ownerType).ToQuery(); if (!typeof(T).Implements <ICacheless>()) { var cache = CacheManager.GetCacheList <T>(command); if (cache != null) { return(cache); } } var sdr = ExecuteReader <T>(command); var result = new List <T>(); try { while (sdr.Read()) { result.Add(TupleToObject <T>(sdr)); } } catch (Exception e) { DwarfContext <T> .GetConfiguration().ErrorLogService.Logg(e); throw new DatabaseOperationException(e.Message, e); } finally { sdr.Close(); } return(typeof(T).Implements <ICacheless>() ? result : CacheManager.SetCacheList(command, result)); }
private static IEnumerable <string> GetCurrentManyToManyTables(IEnumerable <Type> allTypes) { foreach (var type in allTypes) { foreach (var pi in DwarfHelper.GetManyToManyProperties(type)) { var value = ManyToManyAttribute.GetTableName(type, pi.ContainedProperty); if (!string.IsNullOrEmpty(value)) { yield return(value); } } } }
/// <summary> ///Delete two foreign key refernces from the mapping table /// </summary> public virtual void DeleteManyToMany <T>(IDwarf obj1, IDwarf obj2, string alternateTableName = null) { var type1 = obj1.GetType(); var type2 = obj2.GetType(); var tableName = ManyToManyAttribute.GetManyToManyTableName(type1, type2, alternateTableName); DbContextHelper <T> .ClearCacheForType(type1); DbContextHelper <T> .ClearCacheForType(type2); var query = new QueryBuilder() .DeleteFrom("dbo.[" + tableName + "]") .Where("[" + type1.Name + "Id] = " + ValueToSqlString(obj1.Id)) .Where("[" + type2.Name + "Id] = " + ValueToSqlString(obj2.Id)); ExecuteNonQuery <T>(query); }
}//method private void ProcessManyToManyListMember(EntityMemberInfo member, ManyToManyAttribute attr) { var linkEntityType = attr.LinkEntity; var listInfo = member.ChildListInfo = new ChildEntityListInfo(member); listInfo.RelationType = EntityRelationType.ManyToMany; listInfo.LinkEntity = this.Model.GetEntityInfo(linkEntityType, true); listInfo.ParentRefMember = listInfo.LinkEntity.FindEntityRefMember(attr.ThisEntityRef, member.Entity.EntityType, member, this.Log); if (listInfo.ParentRefMember == null) { this.Log.LogError($"Many-to-many setup error: back reference to entity {member.Entity.EntityType} not found in link entity {linkEntityType}."); return; } listInfo.ParentRefMember.ReferenceInfo.TargetListMember = member; var targetEntType = member.DataType.GetGenericArguments()[0]; listInfo.OtherEntityRefMember = listInfo.LinkEntity.FindEntityRefMember(attr.OtherEntityRef, targetEntType, member, this.Log); if (listInfo.OtherEntityRefMember != null) { listInfo.TargetEntity = this.Model.GetEntityInfo(listInfo.OtherEntityRefMember.DataType, true); } }
private static string GetDropTablesScript <T>() { var tables = new StringBuilder(); var manyToManyTables = new StringBuilder(); foreach (var type in DwarfHelper.GetValidTypes <T>()) { foreach (var manyToManyProperty in DwarfHelper.GetManyToManyProperties(type)) { var tableName = ManyToManyAttribute.GetTableName(type, manyToManyProperty.ContainedProperty); if (!manyToManyTables.ToString().Contains("DROP TABLE [" + tableName + "]")) { manyToManyTables.AppendLine("IF EXISTS (SELECT * FROM dbo.sysobjects WHERE Id = OBJECT_ID(N'[" + tableName + "]') AND OBJECTPROPERTY(Id, N'IsUserTable') = 1) DROP Table [" + tableName + "]"); } } if (type.Implements <IDwarf>() && !type.IsAbstract) { tables.AppendLine("IF EXISTS (SELECT * FROM dbo.sysobjects WHERE Id = OBJECT_ID(N'[" + type.Name + "]') AND OBJECTPROPERTY(Id, N'IsUserTable') = 1) DROP Table [" + type.Name + "]"); } } return(manyToManyTables.ToString() + tables); }
public List <Tuple <string, Object> > GetInheritedColumnsAndValues(List <PropertyInfo> inheritedProperties) { List <Tuple <string, Object> > list = new List <Tuple <string, object> >(); foreach (var property in inheritedProperties) { Type propertyType = property.PropertyType; string columnName; ColumnAttribute columnAttribute = (ColumnAttribute)property.GetCustomAttribute(typeof(ColumnAttribute), false); if (columnAttribute == null) { continue; } if (columnAttribute._columnName == null) { columnName = property.Name; } else { columnName = columnAttribute._columnName; } OneToOneAttribute oneToOneAttribute = (OneToOneAttribute)property.GetCustomAttribute(typeof(OneToOneAttribute), false); OneToManyAttribute oneToManyAttribute = (OneToManyAttribute)property.GetCustomAttribute(typeof(OneToManyAttribute), false); ManyToManyAttribute manyToManyAttribute = (ManyToManyAttribute)property.GetCustomAttribute(typeof(ManyToManyAttribute), false); if (oneToOneAttribute == null && oneToManyAttribute == null && manyToManyAttribute == null) { list.Add(new Tuple <string, Object>(columnName, propertyType)); } } return(list); }
/// <summary> /// Automatically handles default persistance operations over DwarfLists for ManyToManyRelationships. /// Should a manual persistance be used via PersistManyToMany, do so before the base call in AppendStore /// </summary> private void PersistManyToManyCollections() { foreach (var pi in DwarfHelper.GetManyToManyProperties(this)) { if (!IsCollectionInitialized(pi.ContainedProperty)) { continue; } var tableName = ManyToManyAttribute.GetTableName(GetType(), pi.ContainedProperty); var obj = (IDwarfList)pi.GetValue(this); foreach (var deletedItem in obj.GetDeletedItems()) { DeleteManyToMany(this, deletedItem, tableName); } foreach (var addedItem in obj.GetAddedItems()) { PersistManyToMany(this, addedItem, tableName); } } }
/// <summary> Write a ManyToMany XML Element from attributes in a member. </summary> public virtual void WriteManyToMany(System.Xml.XmlWriter writer, System.Reflection.MemberInfo member, ManyToManyAttribute attribute, BaseAttribute parentAttribute, System.Type mappedClass) { writer.WriteStartElement( "many-to-many" ); // Attribute: <class> if(attribute.Class != null) writer.WriteAttributeString("class", GetAttributeValue(attribute.Class, mappedClass)); // Attribute: <node> if(attribute.Node != null) writer.WriteAttributeString("node", GetAttributeValue(attribute.Node, mappedClass)); // Attribute: <embed-xml> if( attribute.EmbedXmlSpecified ) writer.WriteAttributeString("embed-xml", attribute.EmbedXml ? "true" : "false"); // Attribute: <entity-name> if(attribute.EntityName != null) writer.WriteAttributeString("entity-name", GetAttributeValue(attribute.EntityName, mappedClass)); // Attribute: <column> if(attribute.Column != null) writer.WriteAttributeString("column", GetAttributeValue(attribute.Column, mappedClass)); // Attribute: <formula> if(attribute.Formula != null) writer.WriteAttributeString("formula", GetAttributeValue(attribute.Formula, mappedClass)); // Attribute: <not-found> if(attribute.NotFound != NotFoundMode.Unspecified) writer.WriteAttributeString("not-found", GetXmlEnumValue(typeof(NotFoundMode), attribute.NotFound)); // Attribute: <outer-join> if(attribute.OuterJoin != OuterJoinStrategy.Unspecified) writer.WriteAttributeString("outer-join", GetXmlEnumValue(typeof(OuterJoinStrategy), attribute.OuterJoin)); // Attribute: <fetch> if(attribute.Fetch != FetchMode.Unspecified) writer.WriteAttributeString("fetch", GetXmlEnumValue(typeof(FetchMode), attribute.Fetch)); // Attribute: <lazy> if(attribute.Lazy != RestrictedLaziness.Unspecified) writer.WriteAttributeString("lazy", GetXmlEnumValue(typeof(RestrictedLaziness), attribute.Lazy)); // Attribute: <foreign-key> if(attribute.ForeignKey != null) writer.WriteAttributeString("foreign-key", GetAttributeValue(attribute.ForeignKey, mappedClass)); // Attribute: <unique> if( attribute.UniqueSpecified ) writer.WriteAttributeString("unique", attribute.Unique ? "true" : "false"); // Attribute: <where> if(attribute.Where != null) writer.WriteAttributeString("where", GetAttributeValue(attribute.Where, mappedClass)); // Attribute: <order-by> if(attribute.OrderBy != null) writer.WriteAttributeString("order-by", GetAttributeValue(attribute.OrderBy, mappedClass)); // Attribute: <property-ref> if(attribute.PropertyRef != null) writer.WriteAttributeString("property-ref", GetAttributeValue(attribute.PropertyRef, mappedClass)); WriteUserDefinedContent(writer, member, null, attribute); System.Collections.ArrayList memberAttribs = GetSortedAttributes(member); int attribPos; // Find the position of the ManyToManyAttribute (its <sub-element>s must be after it) for(attribPos=0; attribPos<memberAttribs.Count; attribPos++) if( memberAttribs[attribPos] is ManyToManyAttribute && ((BaseAttribute)memberAttribs[attribPos]).Position == attribute.Position ) break; // found int i = attribPos + 1; // Element: <meta> for(; i<memberAttribs.Count; i++) { BaseAttribute memberAttrib = memberAttribs[i] as BaseAttribute; if( IsNextElement(memberAttrib, parentAttribute, attribute.GetType()) || IsNextElement(memberAttrib, attribute, typeof(MetaAttribute)) ) break; // next attributes are 'elements' of the same level OR for 'sub-elements' else { if( memberAttrib is ManyToManyAttribute ) break; // Following attributes are for this ManyToMany if( memberAttrib is MetaAttribute ) WriteMeta(writer, member, memberAttrib as MetaAttribute, attribute, mappedClass); } } WriteUserDefinedContent(writer, member, typeof(MetaAttribute), attribute); // Element: <column> for(; i<memberAttribs.Count; i++) { BaseAttribute memberAttrib = memberAttribs[i] as BaseAttribute; if( IsNextElement(memberAttrib, parentAttribute, attribute.GetType()) || IsNextElement(memberAttrib, attribute, typeof(ColumnAttribute)) ) break; // next attributes are 'elements' of the same level OR for 'sub-elements' else { if( memberAttrib is ManyToManyAttribute ) break; // Following attributes are for this ManyToMany if( memberAttrib is ColumnAttribute ) WriteColumn(writer, member, memberAttrib as ColumnAttribute, attribute, mappedClass); } } WriteUserDefinedContent(writer, member, typeof(ColumnAttribute), attribute); // Element: <formula> for(; i<memberAttribs.Count; i++) { BaseAttribute memberAttrib = memberAttribs[i] as BaseAttribute; if( IsNextElement(memberAttrib, parentAttribute, attribute.GetType()) || IsNextElement(memberAttrib, attribute, typeof(FormulaAttribute)) ) break; // next attributes are 'elements' of the same level OR for 'sub-elements' else { if( memberAttrib is ManyToManyAttribute ) break; // Following attributes are for this ManyToMany if( memberAttrib is FormulaAttribute ) WriteFormula(writer, member, memberAttrib as FormulaAttribute, attribute, mappedClass); } } WriteUserDefinedContent(writer, member, typeof(FormulaAttribute), attribute); // Element: <filter> for(; i<memberAttribs.Count; i++) { BaseAttribute memberAttrib = memberAttribs[i] as BaseAttribute; if( IsNextElement(memberAttrib, parentAttribute, attribute.GetType()) || IsNextElement(memberAttrib, attribute, typeof(FilterAttribute)) ) break; // next attributes are 'elements' of the same level OR for 'sub-elements' else { if( memberAttrib is ManyToManyAttribute ) break; // Following attributes are for this ManyToMany if( memberAttrib is FilterAttribute ) WriteFilter(writer, member, memberAttrib as FilterAttribute, attribute, mappedClass); } } WriteUserDefinedContent(writer, member, typeof(FilterAttribute), attribute); writer.WriteEndElement(); }
/// <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); }
public ManyToManyRelation(Table parentTable, Table childTable, MemberInfo member, string memberPath, Type memberValueType, ManyToManyAttribute relationAttribute) : base(parentTable, childTable, member, memberPath, memberValueType, relationAttribute) { }
/// <summary> /// Used to shorten the syntax for initializing ManyToMany lists /// </summary> /// <typeparam name="TY">The type that will populate the DwarfList</typeparam> /// <param name="owningProperty">The current property</param> private DwarfList <TY> InitializeManyToMany <TY>(Expression <Func <T, DwarfList <TY> > > owningProperty) where TY : Dwarf <TY>, new() { var tableName = ManyToManyAttribute.GetTableName(GetType(), ReflectionHelper.GetPropertyInfo(owningProperty)); return(new DwarfList <TY>(LoadManyToManyRelation <TY>(this, tableName))); }