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); }
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); } } }
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()); }