/// <summary> /// Deletes all the objects passed with IDs equal to the passed parameters from the database. /// Relationships are not taken into account in this method /// </summary> /// <param name="conn">SQLite Net connection object</param> /// <param name="primaryKeyValues">Primary keys of the objects to be deleted from the database</param> /// <typeparam name="T">The Entity type, it should match de database entity type</typeparam> public static void DeleteAllIds <T>(this SQLiteConnection conn, IEnumerable <object> primaryKeyValues) { var type = typeof(T); var primaryKeyProperty = type.GetPrimaryKey(); conn.DeleteAllIds(primaryKeyValues.ToArray(), type.GetTableName(), primaryKeyProperty.GetColumnName()); }
static IEnumerable InsertElements(this SQLiteConnection conn, IEnumerable elements, bool replace, ISet <object> objectCache) { if (elements == null) { return(Enumerable.Empty <object>()); } objectCache = objectCache ?? new HashSet <object>(); var elementsToInsert = elements.Cast <object>().Except(objectCache).ToList(); if (elementsToInsert.Count == 0) { return(Enumerable.Empty <object>()); } var primaryKeyProperty = elementsToInsert[0].GetType().GetPrimaryKey(); var isAutoIncrementPrimaryKey = primaryKeyProperty != null && primaryKeyProperty.GetAttribute <AutoIncrementAttribute>() != null; foreach (var element in elementsToInsert) { conn.InsertElement(element, replace, primaryKeyProperty, isAutoIncrementPrimaryKey, objectCache); objectCache.Add(element); } return(elementsToInsert); }
static void InsertElement(this SQLiteConnection conn, object element, bool replace, ISet <object> objectCache) { var primaryKeyProperty = element.GetType().GetPrimaryKey(); var isAutoIncrementPrimaryKey = primaryKeyProperty != null && primaryKeyProperty.GetAttribute <AutoIncrementAttribute>() != null; conn.InsertElement(element, replace, primaryKeyProperty, isAutoIncrementPrimaryKey, objectCache); }
static void InsertElement(this SQLiteConnection conn, object element, bool replace, PropertyInfo primaryKeyProperty, bool isAutoIncrementPrimaryKey, ISet <object> objectCache) { if (element == null || (objectCache != null && objectCache.Contains(element))) { return; } bool isPrimaryKeySet = false; if (replace && isAutoIncrementPrimaryKey) { var primaryKeyValue = primaryKeyProperty.GetValue(element, null); var defaultPrimaryKeyValue = primaryKeyProperty.PropertyType.GetDefault(); isPrimaryKeySet = primaryKeyValue != null && !primaryKeyValue.Equals(defaultPrimaryKeyValue); } bool shouldReplace = replace && (!isAutoIncrementPrimaryKey || isPrimaryKeySet); // Only replace elements that have an assigned primary key if (shouldReplace) { conn.InsertOrReplace(element); } else { conn.Insert(element); } }
public static void GetChildren <T>(this SQLiteConnection conn, ref T element) where T : new() { foreach (var relationshipProperty in typeof(T).GetRelationshipProperties()) { conn.GetChild(ref element, relationshipProperty); } }
private static void UpdateInverseForeignKeys(this SQLiteConnection conn, object element) { foreach (var relationshipProperty in element.GetType().GetRelationshipProperties()) { var relationshipAttribute = relationshipProperty.GetAttribute <RelationshipAttribute>(); // Ignore read-only attributes if (relationshipAttribute.ReadOnly) { continue; } if (relationshipAttribute is OneToManyAttribute) { conn.UpdateOneToManyInverseForeignKey(element, relationshipProperty); } else if (relationshipAttribute is OneToOneAttribute) { conn.UpdateOneToOneInverseForeignKey(element, relationshipProperty); } else if (relationshipAttribute is ManyToManyAttribute) { conn.UpdateManyToManyForeignKeys(element, relationshipProperty); } } }
public static T GetWithChildren <T>(this SQLiteConnection conn, object pk) where T : new() { var element = conn.Get <T>(pk); conn.GetChildren(ref element); return(element); }
public static void GetChild <T>(this SQLiteConnection conn, ref T element, PropertyInfo relationshipProperty) { var relationshipAttribute = relationshipProperty.GetAttribute <RelationshipAttribute>(); if (relationshipAttribute is OneToOneAttribute) { conn.GetOneToOneChild(ref element, relationshipProperty); } else if (relationshipAttribute is OneToManyAttribute) { conn.GetOneToManyChildren(ref element, relationshipProperty); } else if (relationshipAttribute is ManyToOneAttribute) { conn.GetManyToOneChild(ref element, relationshipProperty); } else if (relationshipAttribute is ManyToManyAttribute) { conn.GetManyToManyChildren(ref element, relationshipProperty); } else if (relationshipAttribute is TextBlobAttribute) { TextBlobOperations.GetTextBlobChild(ref element, relationshipProperty); } }
static void InsertValue(this SQLiteConnection conn, object value, bool replace, bool recursive, ISet <object> objectCache) { if (value == null) { return; } var enumerable = value as IEnumerable; if (recursive) { if (enumerable != null) { conn.InsertAllWithChildrenRecursive(enumerable, replace, recursive, objectCache); } else { conn.InsertWithChildrenRecursive(value, replace, recursive, objectCache); } } else { if (enumerable != null) { conn.InsertElements(enumerable, replace, objectCache); } else { conn.InsertElement(value, replace, objectCache); } } }
/// <summary> /// Updates the with foreign keys of the current object and save changes to the database and /// updates the inverse foreign keys of the defined relationships so the relationships are /// stored correctly in the database. This operation will create or delete the required intermediate /// objects for ManyToMany relationships. All related objects must have a primary key assigned in order /// to work correctly. This also implies that any object with 'AutoIncrement' primary key must've been /// inserted in the database previous to this call. /// This method will also update inverse relationships of objects that currently exist in the object tree, /// but it won't update inverse relationships of objects that are not reachable through this object /// properties. For example, objects removed from a 'ToMany' relationship won't be updated in memory. /// </summary> /// <param name="conn">SQLite Net connection object</param> /// <param name="element">Object to be updated. Must already have been inserted in the database</param> public static void UpdateWithChildren(this SQLiteConnection conn, object element) { // Update the current element RefreshForeignKeys(element); conn.Update(element); // Update inverse foreign keys conn.UpdateInverseForeignKeys(element); }
/// <summary> /// Deletes all the objects passed as parameters from the database. /// If recursive flag is set to true, all relationships marked with 'CascadeDelete' will be /// deleted from the database recursively. Inverse relationships and closed entity loops are handled /// correctly to avoid endless loops /// </summary> /// <param name="conn">SQLite Net connection object</param> /// <param name="recursive">If set to <c>true</c> all relationships marked with 'CascadeDelete' will be /// deleted from the database recursively</param> /// <param name="element">Object to be deleted from the database</param> public static void Delete(this SQLiteConnection conn, object element, bool recursive) { if (recursive) { conn.DeleteAll(new [] { element }, recursive); } else { conn.Delete(element); } }
static void DeleteValueRecursive(this SQLiteConnection conn, object value, bool recursive, ISet <object> objectCache) { if (value == null) { return; } var enumerable = value as IEnumerable ?? new [] { value }; conn.DeleteAllRecursive(enumerable, recursive, objectCache); }
private static void GetOneToManyChildren <T>(this SQLiteConnection conn, ref T element, PropertyInfo relationshipProperty) { var type = typeof(T); EnclosedType enclosedType; var entityType = relationshipProperty.GetEntityType(out enclosedType); Debug.Assert(enclosedType != EnclosedType.None, "OneToMany relationship must be a List or Array"); var currentEntityPrimaryKeyProperty = type.GetPrimaryKey(); Debug.Assert(currentEntityPrimaryKeyProperty != null, "OneToMany relationship origin must have Primary Key"); var otherEntityForeignKeyProperty = type.GetForeignKeyProperty(relationshipProperty, inverse: true); Debug.Assert(otherEntityForeignKeyProperty != null, "OneToMany relationship destination must have Foreign Key to the origin class"); var tableMapping = conn.GetMapping(entityType); Debug.Assert(tableMapping != null, "There's no mapping table for OneToMany relationship destination"); var inverseProperty = type.GetInverseProperty(relationshipProperty); IEnumerable values = null; var primaryKeyValue = currentEntityPrimaryKeyProperty.GetValue(element, null); if (primaryKeyValue != null) { var query = string.Format("SELECT * FROM [{0}] WHERE [{1}] = ?", entityType.GetTableName(), otherEntityForeignKeyProperty.GetColumnName()); var softDeleteSql = GetSoftDeleteFilterSql(entityType); if (softDeleteSql != null) { query = String.Concat(query, " AND ", softDeleteSql); } var queryResults = conn.Query(tableMapping, query, primaryKeyValue is Guid ? primaryKeyValue.ToString() : primaryKeyValue); values = CreateEnclosedType(entityType, enclosedType, queryResults); } relationshipProperty.SetValue(element, values, null); if (inverseProperty != null && values != null) { // Establish inverse relationships (we already have that object anyway) foreach (var value in values) { inverseProperty.SetValue(value, element, null); } } }
private static void DeleteAllIds(this SQLiteConnection conn, object[] primaryKeyValues, string entityName, string primaryKeyName) { if (primaryKeyValues == null || primaryKeyValues.Length == 0) { return; } var placeholdersString = string.Join(",", Enumerable.Repeat("?", primaryKeyValues.Length)); var deleteQuery = string.Format("delete from {0} where {1} in ({2})", entityName, primaryKeyName, placeholdersString); conn.Execute(deleteQuery, primaryKeyValues); }
public static void SetChildren <T>(this SQLiteConnection conn, IEnumerable <T> elements, params string[] relationshipPropertyNames) where T : new() { if (elements is ITableQuery <T> ) { throw new InvalidOperationException("Use WithChildren<T> or enumerate the query before calling SetChildren<T>."); } var results = elements as ICollection <T> ?? elements.ToList(); var relationshipProperties = typeof(T).GetRelationshipProperties().ToList(); if (relationshipPropertyNames != null && relationshipPropertyNames.Any()) { foreach (var propertyName in relationshipPropertyNames) { if (relationshipProperties.All(p => p.Name != propertyName)) { throw new ArgumentException(String.Format("Invalid relationship property name '{0}'.", propertyName)); } } var toRemove = relationshipProperties.Where(p => !relationshipPropertyNames.Contains(p.Name)).ToList(); foreach (var p in toRemove) { relationshipProperties.Remove(p); } } foreach (var relationshipProperty in relationshipProperties) { var relationshipAttribute = relationshipProperty.GetAttribute <RelationshipAttribute>(); if (relationshipAttribute is ManyToOneAttribute) { conn.GetManyToOneChildren(results, relationshipProperty); } else if (relationshipAttribute is OneToManyAttribute) { conn.GetOneToManyChildren(results, relationshipProperty); } #if DEBUG else { Debug.WriteLine("WARNING: RelationshipAttribute {0} is not yet supported by WithChildren or SetChildren.", relationshipAttribute.GetType().Name); } #endif } }
static void InsertWithChildrenRecursive(this SQLiteConnection conn, object element, bool replace, bool recursive, ISet <object> objectCache = null) { objectCache = objectCache ?? new HashSet <object>(); if (objectCache.Contains(element)) { return; } conn.InsertElement(element, replace, objectCache); objectCache.Add(element); conn.InsertChildrenRecursive(element, replace, recursive, objectCache); conn.UpdateWithChildren(element); }
static void DeleteAllRecursive(this SQLiteConnection conn, IEnumerable elements, bool recursive, ISet <object> objectCache = null) { if (elements == null) { return; } var isRootElement = objectCache == null; objectCache = objectCache ?? new HashSet <object>(); var elementList = elements.Cast <object>().Except(objectCache).ToList(); // Mark the objects for deletion foreach (var element in elementList) { objectCache.Add(element); } if (recursive) { foreach (var element in elementList) { var type = element.GetType(); foreach (var relationshipProperty in type.GetRelationshipProperties()) { var relationshipAttribute = relationshipProperty.GetAttribute <RelationshipAttribute>(); // Ignore read-only attributes or those that are not marked as CascadeDelete if (!relationshipAttribute.IsCascadeDelete || relationshipAttribute.ReadOnly) { continue; } var value = relationshipProperty.GetValue(element, null); conn.DeleteValueRecursive(value, recursive, objectCache); } } } // To improve performance, the root method call will delete all the objects at once if (isRootElement) { conn.DeleteAllObjects(objectCache); } }
static void DeleteAllObjects(this SQLiteConnection conn, IEnumerable elements) { if (elements == null) { return; } var groupedElements = elements.Cast <object>().GroupBy(o => o.GetType()); foreach (var groupElement in groupedElements) { var type = groupElement.Key; var primaryKeyProperty = type.GetPrimaryKey(); Assert(primaryKeyProperty != null, type, null, "Cannot delete objects without primary key"); var primaryKeyValues = (from element in groupElement select primaryKeyProperty.GetValue(element, null)).ToArray(); conn.DeleteAllIds(primaryKeyValues, type.GetTableName(), primaryKeyProperty.GetColumnName()); } }
private static void GetManyToManyChildren <T>(this SQLiteConnection conn, ref T element, PropertyInfo relationshipProperty) { var type = typeof(T); EnclosedType enclosedType; var entityType = relationshipProperty.GetEntityType(out enclosedType); var currentEntityPrimaryKeyProperty = type.GetPrimaryKey(); var otherEntityPrimaryKeyProperty = entityType.GetPrimaryKey(); var manyToManyMetaInfo = type.GetManyToManyMetaInfo(relationshipProperty); var currentEntityForeignKeyProperty = manyToManyMetaInfo.OriginProperty; var otherEntityForeignKeyProperty = manyToManyMetaInfo.DestinationProperty; var intermediateType = manyToManyMetaInfo.IntermediateType; var tableMapping = conn.GetMapping(entityType); Debug.Assert(enclosedType != EnclosedType.None, "ManyToMany relationship must be a List or Array"); Debug.Assert(currentEntityPrimaryKeyProperty != null, "ManyToMany relationship origin must have Primary Key"); Debug.Assert(otherEntityPrimaryKeyProperty != null, "ManyToMany relationship destination must have Primary Key"); Debug.Assert(intermediateType != null, "ManyToMany relationship intermediate type cannot be null"); Debug.Assert(currentEntityForeignKeyProperty != null, "ManyToMany relationship origin must have a foreign key defined in the intermediate type"); Debug.Assert(otherEntityForeignKeyProperty != null, "ManyToMany relationship destination must have a foreign key defined in the intermediate type"); Debug.Assert(tableMapping != null, "There's no mapping table defined for ManyToMany relationship origin"); IEnumerable values = null; var primaryKeyValue = currentEntityPrimaryKeyProperty.GetValue(element, null); if (primaryKeyValue != null) { // Obtain the relationship keys var keysQuery = string.Format("SELECT [{0}] FROM [{1}] WHERE [{2}] = ?", otherEntityForeignKeyProperty.Name, intermediateType.Name, currentEntityForeignKeyProperty.Name); var query = string.Format("SELECT * FROM [{0}] WHERE [{1}] IN ({2})", entityType.Name, otherEntityPrimaryKeyProperty.Name, keysQuery); var queryResults = conn.Query(tableMapping, query, primaryKeyValue); values = CreateEnclosedType(entityType, enclosedType, queryResults); } relationshipProperty.SetValue(element, values, null); }
static void InsertAllWithChildrenRecursive(this SQLiteConnection conn, IEnumerable elements, bool replace, bool recursive, ISet <object> objectCache = null) { if (elements == null) { return; } objectCache = objectCache ?? new HashSet <object>(); var insertedElements = conn.InsertElements(elements, replace, objectCache).Cast <object>().ToList(); foreach (var element in insertedElements) { conn.InsertChildrenRecursive(element, replace, recursive, objectCache); } foreach (var element in insertedElements) { conn.UpdateWithChildren(element); } }
static void InsertChildrenRecursive(this SQLiteConnection conn, object element, bool replace, bool recursive, ISet <object> objectCache = null) { if (element == null) { return; } objectCache = objectCache ?? new HashSet <object>(); foreach (var relationshipProperty in element.GetType().GetRelationshipProperties()) { var relationshipAttribute = relationshipProperty.GetAttribute <RelationshipAttribute>(); // Ignore read-only attributes and process only 'CascadeInsert' attributes if (relationshipAttribute.ReadOnly || !relationshipAttribute.IsCascadeInsert) { continue; } var value = relationshipProperty.GetValue(element, null); conn.InsertValue(value, replace, recursive, objectCache); } }
private static void GetManyToOneChild <T>(this SQLiteConnection conn, ref T element, PropertyInfo relationshipProperty) { var type = typeof(T); EnclosedType enclosedType; var entityType = relationshipProperty.GetEntityType(out enclosedType); Debug.Assert(enclosedType == EnclosedType.None, "ManyToOne relationship cannot be of type List or Array"); var otherEntityPrimaryKeyProperty = entityType.GetPrimaryKey(); Debug.Assert(otherEntityPrimaryKeyProperty != null, "ManyToOne relationship destination must have Primary Key"); var currentEntityForeignKeyProperty = type.GetForeignKeyProperty(relationshipProperty); Debug.Assert(currentEntityForeignKeyProperty != null, "ManyToOne relationship origin must have Foreign Key"); var tableMapping = conn.GetMapping(entityType); Debug.Assert(tableMapping != null, "There's no mapping table for OneToMany relationship destination"); object value = null; var foreignKeyValue = currentEntityForeignKeyProperty.GetValue(element, null); if (foreignKeyValue != null) { value = conn.Find(foreignKeyValue, tableMapping); if (IsSoftDeleted(entityType, value)) { value = null; } } relationshipProperty.SetValue(element, value, null); }
private static void DeleteAllIds(this SQLiteConnection conn, object[] primaryKeyValues, string entityName, string primaryKeyName) { if (primaryKeyValues == null || primaryKeyValues.Length == 0) { return; } const int limit = 999; if (primaryKeyValues.Length <= limit) { var placeholdersString = string.Join(",", Enumerable.Repeat("?", primaryKeyValues.Length)); var deleteQuery = string.Format("delete from [{0}] where [{1}] in ({2})", entityName, primaryKeyName, placeholdersString); conn.Execute(deleteQuery, primaryKeyValues); } else { foreach (var primaryKeys in Split(primaryKeyValues.ToList(), limit)) { conn.DeleteAllIds(primaryKeys.ToArray(), entityName, primaryKeyName); } } }
/// <summary> /// Inserts all the elements and all the relationships that are annotated with <c>CascadeOperation.CascadeInsert</c> /// into the database. If any element already exists in the database a 'Constraint' exception will be raised. /// Elements with a primary key that it's not <c>AutoIncrement</c> will need a valid identifier before calling /// this method. /// If the <c>recursive</c> flag is set to true, all the relationships annotated with /// <c>CascadeOperation.CascadeInsert</c> are inserted recursively in the database. This method will handle /// loops and inverse relationships correctly. <c>ReadOnly</c> properties will be omitted. /// </summary> /// <param name="conn">SQLite Net connection object</param> /// <param name="elements">Objects to be inserted.</param> /// <param name="recursive">If set to <c>true</c> all the insert-cascade properties will be inserted</param> public static void InsertAllWithChildren(this SQLiteConnection conn, IEnumerable elements, bool recursive = false) { conn.InsertAllWithChildrenRecursive(elements, false, recursive); }
/// <summary> /// Inserts or replace the element and all the relationships that are annotated with /// <c>CascadeOperation.CascadeInsert</c> into the database. If any element already exists in the database /// it will be replaced. Elements with <c>AutoIncrement</c> primary keys that haven't been assigned will /// be always inserted instead of replaced. /// If the <c>recursive</c> flag is set to true, all the relationships annotated with /// <c>CascadeOperation.CascadeInsert</c> are inserted recursively in the database. This method will handle /// loops and inverse relationships correctly. <c>ReadOnly</c> properties will be omitted. /// </summary> /// <param name="conn">SQLite Net connection object</param> /// <param name="element">Object to be inserted.</param> /// <param name="recursive">If set to <c>true</c> all the insert-cascade properties will be inserted</param> public static void InsertOrReplaceWithChildren(this SQLiteConnection conn, object element, bool recursive = false) { conn.InsertWithChildrenRecursive(element, true, recursive); }
private static void UpdateManyToManyForeignKeys(this SQLiteConnection conn, object element, PropertyInfo relationshipProperty) { var type = element.GetType(); EnclosedType enclosedType; var entityType = relationshipProperty.GetEntityType(out enclosedType); var currentEntityPrimaryKeyProperty = type.GetPrimaryKey(); var otherEntityPrimaryKeyProperty = entityType.GetPrimaryKey(); var manyToManyMetaInfo = type.GetManyToManyMetaInfo(relationshipProperty); var currentEntityForeignKeyProperty = manyToManyMetaInfo.OriginProperty; var otherEntityForeignKeyProperty = manyToManyMetaInfo.DestinationProperty; var intermediateType = manyToManyMetaInfo.IntermediateType; Assert(enclosedType != EnclosedType.None, type, relationshipProperty, "ManyToMany relationship must be a List or Array"); Assert(currentEntityPrimaryKeyProperty != null, type, relationshipProperty, "ManyToMany relationship origin must have Primary Key"); Assert(otherEntityPrimaryKeyProperty != null, type, relationshipProperty, "ManyToMany relationship destination must have Primary Key"); Assert(intermediateType != null, type, relationshipProperty, "ManyToMany relationship intermediate type cannot be null"); Assert(currentEntityForeignKeyProperty != null, type, relationshipProperty, "ManyToMany relationship origin must have a foreign key defined in the intermediate type"); Assert(otherEntityForeignKeyProperty != null, type, relationshipProperty, "ManyToMany relationship destination must have a foreign key defined in the intermediate type"); var primaryKey = currentEntityPrimaryKeyProperty.GetValue(element, null); // Obtain the list of children keys var childList = (IEnumerable)relationshipProperty.GetValue(element, null); var childKeyList = (from object child in childList ?? new List <object>() select otherEntityPrimaryKeyProperty.GetValue(child, null)).ToList(); // Check for already existing relationships var childrenPlaceHolders = string.Join(",", Enumerable.Repeat("?", childKeyList.Count)); var currentChildrenQuery = string.Format("select [{0}] from [{1}] where [{2}] == ? and [{0}] in ({3})", otherEntityForeignKeyProperty.GetColumnName(), intermediateType.GetTableName(), currentEntityForeignKeyProperty.GetColumnName(), childrenPlaceHolders); var parameters = new List <object> { primaryKey }; parameters.AddRange(childKeyList); var currentChildKeyList = from object child in conn.Query(conn.GetMapping(intermediateType), currentChildrenQuery, parameters.ToArray()) select otherEntityForeignKeyProperty.GetValue(child, null); // Insert missing relationships in the intermediate table var missingChildKeyList = childKeyList.Where(o => !currentChildKeyList.Contains(o)).ToList(); var missingIntermediateObjects = new List <object>(missingChildKeyList.Count); foreach (var missingChildKey in missingChildKeyList) { var intermediateObject = Activator.CreateInstance(intermediateType); currentEntityForeignKeyProperty.SetValue(intermediateObject, primaryKey, null); otherEntityForeignKeyProperty.SetValue(intermediateObject, missingChildKey, null); missingIntermediateObjects.Add(intermediateObject); } conn.InsertAll(missingIntermediateObjects); // Delete any other pending relationship var deleteQuery = string.Format("delete from [{0}] where [{1}] == ? and [{2}] not in ({3})", intermediateType.GetTableName(), currentEntityForeignKeyProperty.GetColumnName(), otherEntityForeignKeyProperty.GetColumnName(), childrenPlaceHolders); conn.Execute(deleteQuery, parameters.ToArray()); }
private static void UpdateOneToOneInverseForeignKey(this SQLiteConnection conn, object element, PropertyInfo relationshipProperty) { var type = element.GetType(); EnclosedType enclosedType; var entityType = relationshipProperty.GetEntityType(out enclosedType); var originPrimaryKeyProperty = type.GetPrimaryKey(); var inversePrimaryKeyProperty = entityType.GetPrimaryKey(); var inverseForeignKeyProperty = type.GetForeignKeyProperty(relationshipProperty, inverse: true); Assert(enclosedType == EnclosedType.None, type, relationshipProperty, "OneToOne relationships cannot be List or Array of entities"); var inverseProperty = type.GetInverseProperty(relationshipProperty); if (inverseProperty != null) { EnclosedType inverseEnclosedType; var inverseEntityType = inverseProperty.GetEntityType(out inverseEnclosedType); Assert(inverseEnclosedType == EnclosedType.None, type, relationshipProperty, "OneToOne inverse relationship shouldn't be List or Array"); Assert(inverseEntityType == type, type, relationshipProperty, "OneToOne inverse relationship is not the expected type"); } object keyValue = null; if (originPrimaryKeyProperty != null && inverseForeignKeyProperty != null) { keyValue = originPrimaryKeyProperty.GetValue(element, null); } object childKey = null; var child = relationshipProperty.GetValue(element, null); if (child != null) { if (inverseForeignKeyProperty != null && keyValue != null) { inverseForeignKeyProperty.SetValue(child, keyValue, null); } if (inverseProperty != null) { inverseProperty.SetValue(child, element, null); } if (inversePrimaryKeyProperty != null) { childKey = inversePrimaryKeyProperty.GetValue(child, null); } } // Objects already updated, now change the database if (inverseForeignKeyProperty != null && inversePrimaryKeyProperty != null) { var query = string.Format("update [{0}] set [{1}] = ? where [{2}] == ?", entityType.GetTableName(), inverseForeignKeyProperty.GetColumnName(), inversePrimaryKeyProperty.GetColumnName()); conn.Execute(query, keyValue, childKey); // Delete previous relationships var deleteQuery = string.Format("update [{0}] set [{1}] = NULL where [{1}] == ? and [{2}] not in (?)", entityType.GetTableName(), inverseForeignKeyProperty.GetColumnName(), inversePrimaryKeyProperty.GetColumnName()); conn.Execute(deleteQuery, keyValue, childKey ?? ""); } }
private static void UpdateOneToManyInverseForeignKey(this SQLiteConnection conn, object element, PropertyInfo relationshipProperty) { var type = element.GetType(); EnclosedType enclosedType; var entityType = relationshipProperty.GetEntityType(out enclosedType); var originPrimaryKeyProperty = type.GetPrimaryKey(); var inversePrimaryKeyProperty = entityType.GetPrimaryKey(); var inverseForeignKeyProperty = type.GetForeignKeyProperty(relationshipProperty, inverse: true); Assert(enclosedType != EnclosedType.None, type, relationshipProperty, "OneToMany relationships must be List or Array of entities"); Assert(originPrimaryKeyProperty != null, type, relationshipProperty, "OneToMany relationships require Primary Key in the origin entity"); Assert(inversePrimaryKeyProperty != null, type, relationshipProperty, "OneToMany relationships require Primary Key in the destination entity"); Assert(inverseForeignKeyProperty != null, type, relationshipProperty, "Unable to find foreign key for OneToMany relationship"); var inverseProperty = type.GetInverseProperty(relationshipProperty); if (inverseProperty != null) { EnclosedType inverseEnclosedType; var inverseEntityType = inverseProperty.GetEntityType(out inverseEnclosedType); Assert(inverseEnclosedType == EnclosedType.None, type, relationshipProperty, "OneToMany inverse relationship shouldn't be List or Array"); Assert(inverseEntityType == type, type, relationshipProperty, "OneToMany inverse relationship is not the expected type"); } var keyValue = originPrimaryKeyProperty.GetValue(element, null); var children = (IEnumerable)relationshipProperty.GetValue(element, null); var childrenKeyList = new List <object>(); if (children != null) { foreach (var child in children) { var childKey = inversePrimaryKeyProperty.GetValue(child, null); childrenKeyList.Add(childKey); inverseForeignKeyProperty.SetValue(child, keyValue, null); if (inverseProperty != null) { inverseProperty.SetValue(child, element, null); } } } // Objects already updated, now change the database var childrenPlaceHolders = string.Join(",", Enumerable.Repeat("?", childrenKeyList.Count)); var query = string.Format("update [{0}] set [{1}] = ? where [{2}] in ({3})", entityType.GetTableName(), inverseForeignKeyProperty.GetColumnName(), inversePrimaryKeyProperty.GetColumnName(), childrenPlaceHolders); var parameters = new List <object> { keyValue }; parameters.AddRange(childrenKeyList); conn.Execute(query, parameters.ToArray()); // Delete previous relationships var deleteQuery = string.Format("update [{0}] set [{1}] = NULL where [{1}] == ? and [{2}] not in ({3})", entityType.GetTableName(), inverseForeignKeyProperty.GetColumnName(), inversePrimaryKeyProperty.GetColumnName(), childrenPlaceHolders); conn.Execute(deleteQuery, parameters.ToArray()); }
private static void GetOneToManyChildren <T>(this SQLiteConnection conn, ICollection <T> elements, PropertyInfo relationshipProperty) { if (!elements.Any()) { return; } var type = typeof(T); EnclosedType enclosedType; var entityType = relationshipProperty.GetEntityType(out enclosedType); Debug.Assert(enclosedType != EnclosedType.None, "OneToMany relationship must be a List or Array"); var currentEntityPrimaryKeyProperty = type.GetPrimaryKey(); Debug.Assert(currentEntityPrimaryKeyProperty != null, "OneToMany relationship origin must have Primary Key"); var otherEntityForeignKeyProperty = type.GetForeignKeyProperty(relationshipProperty, inverse: true); Debug.Assert(otherEntityForeignKeyProperty != null, "OneToMany relationship destination must have Foreign Key to the origin class"); var tableMapping = conn.GetMapping(entityType); Debug.Assert(tableMapping != null, "There's no mapping table for OneToMany relationship destination"); var inverseProperty = type.GetInverseProperty(relationshipProperty); var primaryKeyValues = elements.Select(x => { var fkPropVal = currentEntityPrimaryKeyProperty.GetValue(x, null); return(fkPropVal is Guid ? fkPropVal.ToString() : fkPropVal); }).Where(x => x != null).Distinct().ToList(); if (primaryKeyValues.Count == 0) { return; } var query = String.Format( "SELECT * FROM [{0}] WHERE [{1}] IN ({2})", entityType.GetTableName(), otherEntityForeignKeyProperty.GetColumnName(), String.Join(",", primaryKeyValues.Select(x => "?")) ); var softDeleteSql = GetSoftDeleteFilterSql(entityType); if (softDeleteSql != null) { query = String.Concat(query, " AND ", softDeleteSql); } var children = conn.Query( tableMapping, query, primaryKeyValues.ToArray() ); foreach (var element in elements) { var primaryKeyValue = currentEntityPrimaryKeyProperty.GetValue(element, null); var queryResults = children.Where(x => otherEntityForeignKeyProperty.GetValue(x, null).Equals(primaryKeyValue)).ToList(); IEnumerable values = null; if (primaryKeyValue != null) { values = CreateEnclosedType(entityType, enclosedType, queryResults); } relationshipProperty.SetValue(element, values, null); if (inverseProperty != null && values != null) { // Establish inverse relationships (we already have that object anyway) foreach (var value in values) { inverseProperty.SetValue(value, element, null); } } } }
/// <summary> /// Deletes all the objects passed as parameters from the database. /// If recursive flag is set to true, all relationships marked with 'CascadeDelete' will be /// deleted from the database recursively. Inverse relationships and closed entity loops are handled /// correctly to avoid endless loops /// </summary> /// <param name="conn">SQLite Net connection object</param> /// <param name="recursive">If set to <c>true</c> all relationships marked with 'CascadeDelete' will be /// deleted from the database recursively</param> /// <param name="objects">Objects to be deleted from the database</param> public static void DeleteAll(this SQLiteConnection conn, IEnumerable objects, bool recursive = false) { conn.DeleteAllRecursive(objects, recursive); }