public void AddOrUpdateMIA(ITransaction transaction, Guid mediaItemId, MediaItemAspect mia, bool add) { MediaItemAspectMetadata miaType; if (!_managedMIATypes.TryGetValue(mia.Metadata.AspectId, out miaType) || miaType == null) throw new ArgumentException(string.Format("MIA_Management: Requested media item aspect type with id '{0}' doesn't exist", mia.Metadata.AspectId)); IList<string> terms1 = new List<string>(); IList<string> terms2 = new List<string>(); IList<BindVar> bindVars = new List<BindVar>(); int ct = 0; string miaTableName = GetMIATableName(miaType); // Attributes: First run foreach (MediaItemAspectMetadata.AttributeSpecification spec in miaType.AttributeSpecifications.Values) { if (mia.IsIgnore(spec)) continue; string attrColName; object attributeValue; string bindVarName = "V" + ct++; switch (spec.Cardinality) { case Cardinality.Inline: attrColName = GetMIAAttributeColumnName(spec); if (add) { terms1.Add(attrColName); terms2.Add("@" + bindVarName); } else terms1.Add(attrColName + " = @" + bindVarName); attributeValue = mia.GetAttributeValue(spec); attributeValue = TruncateBigValue(attributeValue, spec); bindVars.Add(new BindVar(bindVarName, AttributeIsEmpty(attributeValue) ? null : attributeValue, spec.AttributeType)); break; case Cardinality.OneToMany: // After main query break; case Cardinality.ManyToOne: attrColName = GetMIAAttributeColumnName(spec); attributeValue = mia.GetAttributeValue(spec); Guid? insertValue; if (AttributeIsEmpty(attributeValue)) insertValue = null; else { Guid valuePk; GetOrCreateManyToOneMIAAttributeValue(transaction, spec, mia.GetAttributeValue(spec), add, out valuePk); insertValue = valuePk; } if (add) { terms1.Add(attrColName); terms2.Add("@" + bindVarName); } else terms1.Add(attrColName + " = @" + bindVarName); bindVars.Add(new BindVar(bindVarName, insertValue.HasValue ? (Guid?) insertValue.Value : null, typeof(Guid))); break; case Cardinality.ManyToMany: // After main query break; default: throw new NotImplementedException(string.Format("Cardinality '{0}' for attribute '{1}.{2}' is not implemented", spec.Cardinality, miaType.AspectId, spec.AttributeName)); } } // terms = all inline attributes // sqlValues = all inline attribute values if (add || terms1.Count > 0) { // Main query StringBuilder mainQueryBuilder = new StringBuilder(); if (add) { mainQueryBuilder.Append("INSERT INTO "); mainQueryBuilder.Append(miaTableName); mainQueryBuilder.Append(" ("); } else { mainQueryBuilder.Append("UPDATE "); mainQueryBuilder.Append(miaTableName); mainQueryBuilder.Append(" SET "); } mainQueryBuilder.Append(StringUtils.Join(", ", terms1)); bindVars.Add(new BindVar("MEDIA_ITEM_ID", mediaItemId, typeof(Guid))); // values = all inline attribute values plus media item ID if (add) { if (terms1.Count > 0) mainQueryBuilder.Append(", "); mainQueryBuilder.Append(MIA_MEDIA_ITEM_ID_COL_NAME); // Append the ID column as a normal attribute mainQueryBuilder.Append(") VALUES ("); terms2.Add("@MEDIA_ITEM_ID"); mainQueryBuilder.Append(StringUtils.Join(", ", terms2)); mainQueryBuilder.Append(")"); } else { mainQueryBuilder.Append(" WHERE "); mainQueryBuilder.Append(MIA_MEDIA_ITEM_ID_COL_NAME); // Use the ID column in WHERE condition mainQueryBuilder.Append(" = @MEDIA_ITEM_ID"); } ISQLDatabase database = transaction.Database; using (IDbCommand command = transaction.CreateCommand()) { command.CommandText = mainQueryBuilder.ToString(); foreach (BindVar bindVar in bindVars) database.AddParameter(command, bindVar.Name, bindVar.Value, bindVar.VariableType); command.ExecuteNonQuery(); } } // Attributes: Second run foreach (MediaItemAspectMetadata.AttributeSpecification spec in miaType.AttributeSpecifications.Values) { if (mia.IsIgnore(spec)) continue; switch (spec.Cardinality) { case Cardinality.Inline: break; case Cardinality.OneToMany: InsertOrUpdateOneToManyMIAAttributeValues(transaction, spec, mediaItemId, mia.GetCollectionAttribute(spec), add); break; case Cardinality.ManyToOne: break; case Cardinality.ManyToMany: InsertOrUpdateManyToManyMIAAttributeValues(transaction, spec, mediaItemId, mia.GetCollectionAttribute(spec), add); break; default: throw new NotImplementedException(string.Format("Cardinality '{0}' for attribute '{1}.{2}' is not implemented", spec.Cardinality, miaType.AspectId, spec.AttributeName)); } } CleanupAllManyToOneOrphanedAttributeValues(transaction, miaType); }