public FirstCharGroupingFunction(MediaItemAspectMetadata.AttributeSpecification attributeType)
{
_attributeType = attributeType;
if (_emptyOrMiscCharacterGroupName == null)
_emptyOrMiscCharacterGroupName = LocalizationHelper.Translate(EMPTY_OR_MISC_CHAR_GROUP_NAME_RES);
// TODO: How to get all valid letters in all variants (with umlauts etc.)?
_letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz";
}
public override bool Equals(object obj)
{
MediaItemAspectMetadata other = obj as MediaItemAspectMetadata;
if (other == null)
{
return(false);
}
return(other.AspectId == _aspectId);
}
/// <summary>
/// Creates a new <see cref="ComplexAttributeQueryBuilder"/> instance.
/// </summary>
/// <param name="miaManagement">MIAM management instance from media library.</param>
/// <param name="complexQueryAttribute">Complex attribute, which is requested by this query. Only attributes
/// with a cardinality different from <see cref="Cardinality.Inline"/> are allowed here.</param>
/// <param name="selectProjectionFunction">This delegate function will be called for the selected attribute.
/// It must return an SQL projection expression whose return value is the requested value for that attribute.
/// If this delegate function is <c>null</c>, the actual attribute is selected without a projection function.</param>
/// <param name="necessaryRequestedMIAs">MIAs which must be present for the media item to match the query.</param>
/// <param name="filter">Filter which must be applied to the media items to match the query.</param>
public ComplexAttributeQueryBuilder(
MIA_Management miaManagement,
MediaItemAspectMetadata.AttributeSpecification complexQueryAttribute,
SelectProjectionFunction selectProjectionFunction,
IEnumerable<MediaItemAspectMetadata> necessaryRequestedMIAs, IFilter filter) : base(miaManagement)
{
_queryAttribute = complexQueryAttribute;
_selectProjectionFunction = selectProjectionFunction;
_necessaryRequestedMIAs = necessaryRequestedMIAs;
_filter = filter;
}
public void RegisterLocallyKnownMediaItemAspectType(MediaItemAspectMetadata miaType)
{
if (_locallyKnownMediaItemAspectTypes.ContainsKey(miaType.AspectId))
return;
_locallyKnownMediaItemAspectTypes.Add(miaType.AspectId, miaType);
IServerConnectionManager serverConnectionManager = ServiceRegistration.Get<IServerConnectionManager>();
IContentDirectory cd = serverConnectionManager == null ? null :
serverConnectionManager.ContentDirectory;
if (cd != null)
cd.AddMediaItemAspectStorage(miaType);
}
public CompiledGroupedAttributeValueQuery(
MIA_Management miaManagement,
IEnumerable<MediaItemAspectMetadata> necessaryRequestedMIATypes,
MediaItemAspectMetadata.AttributeSpecification selectedAttribute, IAttributeFilter selectAttributeFilter,
SelectProjectionFunction selectProjectionFunction, Type projectionValueType,
IFilter filter)
{
_miaManagement = miaManagement;
_necessaryRequestedMIATypes = necessaryRequestedMIATypes;
_selectAttribute = selectedAttribute;
_selectAttributeFilter = selectAttributeFilter;
_selectProjectionFunction = selectProjectionFunction;
_projectionValueType = projectionValueType;
_filter = filter;
}
public QueryAttribute(MediaItemAspectMetadata.AttributeSpecification attr)
{
_attr = attr;
}
/// <summary>
/// Creates a table query of the main media item aspect table of the given <paramref name="miaType"/>.
/// </summary>
/// <param name="miaManagement">MIA management instance.</param>
/// <param name="miaType">Type of the MIA to request.</param>
/// <returns>Table query for the given MIA.</returns>
public static TableQueryData CreateTableQueryOfMIATable(MIA_Management miaManagement,
MediaItemAspectMetadata miaType)
{
return new TableQueryData(miaManagement.GetMIATableName(miaType));
}
public bool AddMediaItemAspectStorage(MediaItemAspectMetadata miam)
{
lock (_syncObj)
{
if (_managedMIATypes.ContainsKey(miam.AspectId))
return false;
_managedMIATypes.Add(miam.AspectId, null);
}
ISQLDatabase database = ServiceRegistration.Get<ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
ServiceRegistration.Get<ILogger>().Info("MIA_Management: Adding media library storage for media item aspect '{0}' (id '{1}')",
miam.Name, miam.AspectId);
try
{
// Register metadata first - generated aliases will reference to the new MIA type row
using (IDbCommand command = MediaLibrary_SubSchema.CreateMediaItemAspectMetadataCommand(transaction, miam.AspectId, miam.Name, miam.Serialize()))
command.ExecuteNonQuery();
// Create main table for new MIA type
string miaTableName = GenerateMIATableName(transaction, miam);
StringBuilder mainStatementBuilder = new StringBuilder("CREATE TABLE " + miaTableName + " (" +
MIA_MEDIA_ITEM_ID_COL_NAME + " " + database.GetSQLType(typeof(Guid)) + ", ");
IList<string> terms = new List<string>();
IList<string> additionalAttributesConstraints = new List<string>();
string collectionAttributeTableName;
string pkConstraintName;
// Attributes: First run
foreach (MediaItemAspectMetadata.AttributeSpecification spec in miam.AttributeSpecifications.Values)
{
string sqlType = spec.AttributeType == typeof(string) ? database.GetSQLVarLengthStringType(spec.MaxNumChars) :
database.GetSQLType(spec.AttributeType);
string attributeColumnName = GenerateMIAAttributeColumnName(transaction, spec);
switch (spec.Cardinality)
{
case Cardinality.Inline:
terms.Add(attributeColumnName + " " + sqlType);
break;
case Cardinality.OneToMany:
GenerateMIACollectionAttributeTableName(transaction, spec);
break;
case Cardinality.ManyToOne:
// Create foreign table - the join attribute will be located in the main MIA table
// We need to create the "One" table first because the main table references on it
collectionAttributeTableName = GenerateMIACollectionAttributeTableName(transaction, spec);
pkConstraintName = GenerateDBObjectName(transaction, miam.AspectId, collectionAttributeTableName + "_PK", "PK");
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = "CREATE TABLE " + collectionAttributeTableName + " (" +
FOREIGN_COLL_ATTR_ID_COL_NAME + " " + database.GetSQLType(typeof(Guid)) + ", " +
COLL_ATTR_VALUE_COL_NAME + " " + sqlType + ", " +
"CONSTRAINT " + pkConstraintName + " PRIMARY KEY (" + FOREIGN_COLL_ATTR_ID_COL_NAME + ")" +
")";
ServiceRegistration.Get<ILogger>().Debug("MIA_Management: Creating MTO table '{0}' for attribute '{1}' in media item aspect '{2}'",
collectionAttributeTableName, spec.AttributeName, miam.AspectId);
command.ExecuteNonQuery();
}
// Create foreign table - the join attribute will be located in the main MIA table
string fkMediaItemConstraintName = GenerateDBObjectName(transaction, miam.AspectId, "MIA_" + collectionAttributeTableName + "_FK", "FK");
terms.Add(attributeColumnName + " " + database.GetSQLType(typeof(Guid)));
additionalAttributesConstraints.Add("CONSTRAINT " + fkMediaItemConstraintName +
" FOREIGN KEY (" + attributeColumnName + ")" +
" REFERENCES " + collectionAttributeTableName + " (" + FOREIGN_COLL_ATTR_ID_COL_NAME + ") ON DELETE SET NULL");
break;
case Cardinality.ManyToMany:
GenerateMIACollectionAttributeTableName(transaction, spec);
break;
default:
throw new NotImplementedException(string.Format("Cardinality '{0}' for attribute '{1}.{2}' is not implemented",
spec.Cardinality, miam.AspectId, spec.AttributeName));
}
}
// Main table
foreach (string term in terms)
{
mainStatementBuilder.Append(term);
mainStatementBuilder.Append(", ");
}
string pkConstraintName1 = GenerateDBObjectName(transaction, miam.AspectId, miaTableName + "_PK", "PK");
string fkMediaItemConstraintName1 = GenerateDBObjectName(transaction, miam.AspectId, miaTableName + "_MEDIA_ITEMS_FK", "FK");
mainStatementBuilder.Append(
"CONSTRAINT " + pkConstraintName1 + " PRIMARY KEY (" + MIA_MEDIA_ITEM_ID_COL_NAME + "), " +
"CONSTRAINT " + fkMediaItemConstraintName1 +
" FOREIGN KEY (" + MIA_MEDIA_ITEM_ID_COL_NAME + ") REFERENCES " +
MediaLibrary_SubSchema.MEDIA_ITEMS_TABLE_NAME + " (" + MediaLibrary_SubSchema.MEDIA_ITEMS_ITEM_ID_COL_NAME + ") ON DELETE CASCADE");
if (additionalAttributesConstraints.Count > 0)
{
mainStatementBuilder.Append(", ");
mainStatementBuilder.Append(StringUtils.Join(", ", additionalAttributesConstraints));
}
mainStatementBuilder.Append(")");
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = mainStatementBuilder.ToString();
ServiceRegistration.Get<ILogger>().Debug(
"MIA_Management: Creating main table '{0}' for media item aspect '{1}'", miaTableName, miam.AspectId);
command.ExecuteNonQuery();
}
string indexName = GenerateDBObjectName(transaction, miam.AspectId, miaTableName + "_PK_IDX", "IDX");
ServiceRegistration.Get<ILogger>().Debug("MIA_Management: Creating primary key index '{0}' for media item aspect '{1}'",
indexName, miam.AspectId);
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = "CREATE UNIQUE INDEX " + indexName + " ON " + miaTableName + "(" + MIA_MEDIA_ITEM_ID_COL_NAME + ")";
command.ExecuteNonQuery();
}
// Attributes: Second run
foreach (MediaItemAspectMetadata.AttributeSpecification spec in miam.AttributeSpecifications.Values)
{
string sqlType = spec.AttributeType == typeof(string) ? database.GetSQLVarLengthStringType(spec.MaxNumChars) :
database.GetSQLType(spec.AttributeType);
string attributeColumnName = GetMIAAttributeColumnName(spec); // Name was already generated in previous loop
switch (spec.Cardinality)
{
case Cardinality.Inline:
if (spec.IsIndexed)
{
// Value index
indexName = GenerateDBObjectName(transaction, miam.AspectId, attributeColumnName + "_IDX", "IDX");
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = "CREATE INDEX " + indexName + " ON " + miaTableName + "(" + attributeColumnName + ")";
ServiceRegistration.Get<ILogger>().Debug(
"MIA_Management: Creating index '{0}' for inline attribute '{1}' in media item aspect '{2}'",
indexName, spec.AttributeName, miam.AspectId);
command.ExecuteNonQuery();
}
}
break;
case Cardinality.OneToMany:
// Create foreign table with the join attribute inside
collectionAttributeTableName = GetMIACollectionAttributeTableName(spec); // Name was already generated in previous loop
pkConstraintName = GenerateDBObjectName(transaction, miam.AspectId, collectionAttributeTableName + "_PK", "PK");
string fkMediaItemConstraintName = GenerateDBObjectName(transaction, miam.AspectId, collectionAttributeTableName + "_MEDIA_ITEM_FK", "FK");
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = "CREATE TABLE " + collectionAttributeTableName + " (" +
MIA_MEDIA_ITEM_ID_COL_NAME + " " + database.GetSQLType(typeof(Guid)) + ", " +
COLL_ATTR_VALUE_COL_NAME + " " + sqlType + ", " +
"CONSTRAINT " + pkConstraintName + " PRIMARY KEY (" + MIA_MEDIA_ITEM_ID_COL_NAME + "), " +
"CONSTRAINT " + fkMediaItemConstraintName +
" FOREIGN KEY (" + MIA_MEDIA_ITEM_ID_COL_NAME + ")" +
" REFERENCES " + MediaLibrary_SubSchema.MEDIA_ITEMS_TABLE_NAME + " (" + MediaLibrary_SubSchema.MEDIA_ITEMS_ITEM_ID_COL_NAME + ") ON DELETE CASCADE" +
")";
ServiceRegistration.Get<ILogger>().Debug(
"MIA_Management: Creating OTM table '{0}' for attribute '{1}' in media item aspect '{2}'",
collectionAttributeTableName, spec.AttributeName, miam.AspectId);
command.ExecuteNonQuery();
}
// Foreign key index
indexName = GenerateDBObjectName(transaction, miam.AspectId, collectionAttributeTableName + "_FK_IDX", "IDX");
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = "CREATE INDEX " + indexName + " ON " + collectionAttributeTableName + "(" +
MIA_MEDIA_ITEM_ID_COL_NAME + ")";
ServiceRegistration.Get<ILogger>().Debug("MIA_Management: Creating foreign key index '{0}' for OTM attribute '{1}' in media item aspect '{2}'",
indexName, spec.AttributeName, miam.AspectId);
command.ExecuteNonQuery();
}
if (spec.IsIndexed)
{
// Value index
indexName = GenerateDBObjectName(transaction, miam.AspectId, collectionAttributeTableName + "_VAL_IDX", "IDX");
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = "CREATE INDEX " + indexName + " ON " + collectionAttributeTableName + "(" +
COLL_ATTR_VALUE_COL_NAME + ")";
ServiceRegistration.Get<ILogger>().Debug(
"MIA_Management: Creating value index '{0}' for OTM attribute '{1}' in media item aspect '{2}'",
indexName, spec.AttributeName, miam.AspectId);
command.ExecuteNonQuery();
}
}
break;
case Cardinality.ManyToOne:
collectionAttributeTableName = GetMIACollectionAttributeTableName(spec); // Name was already generated in previous loop
if (spec.IsIndexed)
{
// Foreign key index
indexName = GenerateDBObjectName(transaction, miam.AspectId, collectionAttributeTableName + "_FK_IDX", "IDX");
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = "CREATE INDEX " + indexName + " ON " + miaTableName + "(" +
attributeColumnName + ")";
ServiceRegistration.Get<ILogger>().Debug(
"MIA_Management: Creating foreign key index '{0}' for MTO attribute '{1}' in media item aspect '{2}'",
indexName, spec.AttributeName, miam.AspectId);
command.ExecuteNonQuery();
}
}
// Value index
indexName = GenerateDBObjectName(transaction, miam.AspectId, collectionAttributeTableName + "_VAL_IDX", "IDX");
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = "CREATE UNIQUE INDEX " + indexName + " ON " + collectionAttributeTableName + "(" +
COLL_ATTR_VALUE_COL_NAME + ")";
ServiceRegistration.Get<ILogger>().Debug(
"MIA_Management: Creating value index '{0}' for MTO attribute '{1}' in media item aspect '{2}'",
indexName, spec.AttributeName, miam.AspectId);
command.ExecuteNonQuery();
}
break;
case Cardinality.ManyToMany:
// Create foreign table and additional table for the N:M join attributes
collectionAttributeTableName = GetMIACollectionAttributeTableName(spec); // Name was already generated in previous loop
pkConstraintName = GenerateDBObjectName(transaction, miam.AspectId, collectionAttributeTableName + "_PK", "PK");
string nmTableName = GenerateMIACollectionAttributeNMTableName(transaction, spec);
string pkNMConstraintName = GenerateDBObjectName(transaction, miam.AspectId, nmTableName + "_PK", "PK");
string fkMainTableConstraintName = GenerateDBObjectName(transaction, miam.AspectId, nmTableName + "_MAIN_FK", "FK");
string fkForeignTableConstraintName = GenerateDBObjectName(transaction, miam.AspectId, nmTableName + "_FOREIGN_FK", "PK");
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = "CREATE TABLE " + collectionAttributeTableName + " (" +
FOREIGN_COLL_ATTR_ID_COL_NAME + " " + database.GetSQLType(typeof(Guid)) + ", " +
COLL_ATTR_VALUE_COL_NAME + " " + sqlType + ", " +
"CONSTRAINT " + pkConstraintName + " PRIMARY KEY (" + FOREIGN_COLL_ATTR_ID_COL_NAME + ")" + ")";
ServiceRegistration.Get<ILogger>().Debug(
"MIA_Management: Creating MTM value table '{0}' for attribute '{1}' in media item aspect '{2}'",
collectionAttributeTableName, spec.AttributeName, miam.AspectId);
command.ExecuteNonQuery();
}
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = "CREATE TABLE " + nmTableName + " (" +
MIA_MEDIA_ITEM_ID_COL_NAME + " " + database.GetSQLType(typeof(Guid)) + ", " +
FOREIGN_COLL_ATTR_ID_COL_NAME + " " + database.GetSQLType(typeof(Guid)) + ", " +
"CONSTRAINT " + pkNMConstraintName + " PRIMARY KEY (" + MIA_MEDIA_ITEM_ID_COL_NAME + "," + FOREIGN_COLL_ATTR_ID_COL_NAME + "), " +
"CONSTRAINT " + fkMainTableConstraintName + " FOREIGN KEY (" + MIA_MEDIA_ITEM_ID_COL_NAME + ")" +
" REFERENCES " + miaTableName + " (" + MediaLibrary_SubSchema.MEDIA_ITEMS_ITEM_ID_COL_NAME + ") ON DELETE CASCADE, " +
"CONSTRAINT " + fkForeignTableConstraintName + " FOREIGN KEY (" + FOREIGN_COLL_ATTR_ID_COL_NAME + ")" +
" REFERENCES " + collectionAttributeTableName + " (" + FOREIGN_COLL_ATTR_ID_COL_NAME + ") ON DELETE CASCADE" +
")";
ServiceRegistration.Get<ILogger>().Debug(
"MIA_Management: Creating N:M table '{0}' for MTM attribute '{1}' in media item aspect '{2}'",
nmTableName, spec.AttributeName, miam.AspectId);
command.ExecuteNonQuery();
}
// Foreign key index to MIA table
indexName = GenerateDBObjectName(transaction, miam.AspectId, nmTableName + "_MIA_FK_IDX", "IDX");
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = "CREATE INDEX " + indexName + " ON " + nmTableName + "(" +
MIA_MEDIA_ITEM_ID_COL_NAME + ")";
ServiceRegistration.Get<ILogger>().Debug(
"MIA_Management: Creating foreign index '{0}' to main MIA table for MTM attribute '{1}' in media item aspect '{2}'",
indexName, spec.AttributeName, miam.AspectId);
command.ExecuteNonQuery();
}
// Foreign key index to value table
indexName = GenerateDBObjectName(transaction, miam.AspectId, nmTableName + "_VAL_FK_IDX", "IDX");
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = "CREATE INDEX " + indexName + " ON " + nmTableName + "(" +
FOREIGN_COLL_ATTR_ID_COL_NAME + ")";
ServiceRegistration.Get<ILogger>().Debug(
"MIA_Management: Creating foreign index '{0}' to value table for MTM attribute '{1}' in media item aspect '{2}'",
indexName, spec.AttributeName, miam.AspectId);
command.ExecuteNonQuery();
}
if (spec.IsIndexed)
{
// Value index
indexName = GenerateDBObjectName(transaction, miam.AspectId, collectionAttributeTableName + "_VAL_IDX", "IDX");
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = "CREATE UNIQUE INDEX " + indexName + " ON " + collectionAttributeTableName + "(" +
COLL_ATTR_VALUE_COL_NAME + ")";
ServiceRegistration.Get<ILogger>().Debug(
"MIA_Management: Creating value index '{0}' for MTM attribute '{1}' in media item aspect '{2}'",
indexName, spec.AttributeName, miam.AspectId);
command.ExecuteNonQuery();
}
}
break;
default:
throw new NotImplementedException(string.Format("Cardinality '{0}' for attribute '{1}.{2}' is not implemented",
spec.Cardinality, miam.AspectId, spec.AttributeName));
}
}
transaction.Commit();
}
catch (Exception e)
{
ServiceRegistration.Get<ILogger>().Error("MIA_Management: Error adding media item aspect storage '{0}'", e, miam.AspectId);
transaction.Rollback();
throw;
}
lock (_syncObj)
_managedMIATypes[miam.AspectId] = miam;
return true;
}
protected void InsertOrUpdateManyToManyMIAAttributeValues(ITransaction transaction,
MediaItemAspectMetadata.AttributeSpecification spec, Guid mediaItemId, IEnumerable values, bool insert)
{
LockAttribute(spec);
try
{
if (!insert)
DeleteManyToManyAttributeAssociationsNotInEnumeration(transaction, spec, mediaItemId, values);
if (values != null)
foreach (object value in values)
InsertOrUpdateManyToManyMIAAttributeValue(transaction, spec, mediaItemId, value);
if (!insert)
CleanupManyToManyOrphanedAttributeValues(transaction, spec);
}
finally
{
UnlockAttribute(spec);
}
}
protected void CleanupManyToManyOrphanedAttributeValues(ITransaction transaction,
MediaItemAspectMetadata.AttributeSpecification spec)
{
string collectionAttributeTableName = GetMIACollectionAttributeTableName(spec);
string nmTableName = GetMIACollectionAttributeNMTableName(spec);
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = "DELETE FROM " + collectionAttributeTableName + " WHERE NOT EXISTS (" +
"SELECT " + FOREIGN_COLL_ATTR_ID_COL_NAME + " FROM " + nmTableName + " NM WHERE " +
FOREIGN_COLL_ATTR_ID_COL_NAME + " = " + collectionAttributeTableName + "." + FOREIGN_COLL_ATTR_ID_COL_NAME + ")";
command.ExecuteNonQuery();
}
}
protected void GetOrCreateManyToOneMIAAttributeValue(ITransaction transaction,
MediaItemAspectMetadata.AttributeSpecification spec, object value, bool insert, out Guid valuePk)
{
string collectionAttributeTableName = GetMIACollectionAttributeTableName(spec);
ISQLDatabase database = transaction.Database;
LockAttribute(spec);
try
{
using (IDbCommand command = transaction.CreateCommand())
{
// First check if value already exists...
command.CommandText = "SELECT " + FOREIGN_COLL_ATTR_ID_COL_NAME + " FROM " + collectionAttributeTableName +
" WHERE " + COLL_ATTR_VALUE_COL_NAME + " = @COLL_ATTR_VALUE";
database.AddParameter(command, "COLL_ATTR_VALUE", value, spec.AttributeType);
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
valuePk = database.ReadDBValue<Guid>(reader, 0);
return;
}
}
// ... if not, insert it
valuePk = Guid.NewGuid();
command.CommandText = "INSERT INTO " + collectionAttributeTableName + " (" +
FOREIGN_COLL_ATTR_ID_COL_NAME + ", " + COLL_ATTR_VALUE_COL_NAME + ") VALUES (@FOREIGN_COLL_ATTR_ID, @COLL_ATTR_VALUE)";
database.AddParameter(command, "FOREIGN_COLL_ATTR_ID", valuePk, typeof(Guid));
command.ExecuteNonQuery();
}
}
finally
{
UnlockAttribute(spec);
}
}
internal string GenerateMIATableName(ITransaction transaction, MediaItemAspectMetadata miam)
{
string identifier = GetMIATableIdentifier(miam);
return GenerateDBObjectName(transaction, miam.AspectId, identifier, "M_" + miam.Name);
}
/// <summary>
/// Gets the actual table name for a MIAM collection attribute table.
/// </summary>
/// <returns>Table name for the table containing the specified collection attribute.</returns>
internal string GetMIACollectionAttributeNMTableName(MediaItemAspectMetadata.AttributeSpecification spec)
{
string identifier = GetMIACollectionAttributeNMTableIdentifier(spec);
return GetAliasMapping(identifier, string.Format("Attribute '{0}' of MIAM '{1}' (id: '{2}') doesn't have a corresponding N:M table name yet",
spec.AttributeName, spec.ParentMIAM.Name, spec.ParentMIAM.AspectId));
}
/// <summary>
/// Gets the actual table name for a MIAM table.
/// </summary>
/// <returns>Table name for the table containing the inline attributes of the specified <paramref name="miam"/>.</returns>
internal string GetMIATableName(MediaItemAspectMetadata miam)
{
string identifier = GetMIATableIdentifier(miam);
return GetAliasMapping(identifier, string.Format("MIAM '{0}' (id: '{1}') doesn't have a corresponding table name yet",
miam.Name, miam.AspectId));
}
/// <summary>
/// Gets a technical table identifier for the N:M table for the given MIAM collection attribute.
/// </summary>
/// <returns>Table identifier for the N:M table for the given collection attribute. The returned identifier must be
/// mapped to a shortened table name to be used in the DB.</returns>
internal string GetMIACollectionAttributeNMTableIdentifier(MediaItemAspectMetadata.AttributeSpecification spec)
{
return "NM_" + GetMIATableName(spec.ParentMIAM) + "_" + SqlUtils.ToSQLIdentifier(spec.AttributeName);
}
/// <summary>
/// Creates a new media item aspect instance for the specified media item aspect <paramref name="metadata"/>.
/// </summary>
/// <param name="metadata">Media item aspect specification.</param>
public MediaItemAspect(MediaItemAspectMetadata metadata)
{
_metadata = metadata;
Initialize();
}
internal string GenerateMIACollectionAttributeNMTableName(ITransaction transaction, MediaItemAspectMetadata.AttributeSpecification spec)
{
string identifier = GetMIACollectionAttributeNMTableIdentifier(spec);
return GenerateDBObjectName(transaction, spec.ParentMIAM.AspectId, identifier, "NM_" + spec.AttributeName);
}
protected void DeleteManyToManyAttributeAssociationsNotInEnumeration(ITransaction transaction,
MediaItemAspectMetadata.AttributeSpecification spec, Guid mediaItemId, IEnumerable values)
{
string collectionAttributeTableName = GetMIACollectionAttributeTableName(spec);
string nmTableName = GetMIACollectionAttributeNMTableName(spec);
ISQLDatabase database = transaction.Database;
using (IDbCommand command = transaction.CreateCommand())
{
database.AddParameter(command, "MEDIA_ITEM_ID", mediaItemId, typeof(Guid));
IList<string> bindVars = new List<string>();
int ct = 0;
if (values != null)
foreach (object value in values)
{
string bindVar = "V" + ct++;
bindVars.Add("@" + bindVar);
database.AddParameter(command, bindVar, value, spec.AttributeType);
}
string commandText = "DELETE FROM " + nmTableName + " WHERE " + MIA_MEDIA_ITEM_ID_COL_NAME + " = @MEDIA_ITEM_ID";
if (bindVars.Count > 0)
commandText += " AND NOT EXISTS(" +
"SELECT " + FOREIGN_COLL_ATTR_ID_COL_NAME + " FROM " + collectionAttributeTableName + " V WHERE V." +
FOREIGN_COLL_ATTR_ID_COL_NAME + " = " + nmTableName + "." + FOREIGN_COLL_ATTR_ID_COL_NAME +
" AND " + COLL_ATTR_VALUE_COL_NAME + " IN (" + StringUtils.Join(", ", bindVars) + "))";
command.CommandText = commandText;
command.ExecuteNonQuery();
}
}
protected static object TruncateBigValue(object value, MediaItemAspectMetadata.AttributeSpecification attributeSpecification)
{
string str = value as string;
uint maxNumChars = attributeSpecification.MaxNumChars;
if (!string.IsNullOrEmpty(str) && maxNumChars > 0 && str.Length > maxNumChars)
return str.Substring(0, (int) maxNumChars);
return value;
}
protected void InsertOrUpdateManyToManyMIAAttributeValue(ITransaction transaction,
MediaItemAspectMetadata.AttributeSpecification spec, Guid mediaItemId, object value)
{
string collectionAttributeTableName = GetMIACollectionAttributeTableName(spec);
IDatabaseManager databaseManager = ServiceRegistration.Get<IDatabaseManager>();
ISQLDatabase database = transaction.Database;
// Insert value into collection attribute table if not exists: We do it in a single statement to avoid rountrips to the DB
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = "INSERT INTO " + collectionAttributeTableName + " (" +
FOREIGN_COLL_ATTR_ID_COL_NAME + ", " + COLL_ATTR_VALUE_COL_NAME + ") SELECT @FOREIGN_COLL_ATTR, @COLL_ATTR_VALUE FROM " +
databaseManager.DummyTableName + " WHERE NOT EXISTS(SELECT " + FOREIGN_COLL_ATTR_ID_COL_NAME +
" FROM " + collectionAttributeTableName + " WHERE " + COLL_ATTR_VALUE_COL_NAME + " = @COLL_ATTR_VALUE)";
database.AddParameter(command, "FOREIGN_COLL_ATTR", Guid.NewGuid(), typeof(Guid));
value = TruncateBigValue(value, spec);
database.AddParameter(command, "COLL_ATTR_VALUE", value, spec.AttributeType); // Used twice in query
command.ExecuteNonQuery();
}
// Check association: We do it here with a single statement to avoid roundtrips to the DB
string nmTableName = GetMIACollectionAttributeNMTableName(spec);
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = "INSERT INTO " + nmTableName + " (" + MIA_MEDIA_ITEM_ID_COL_NAME + ", " + FOREIGN_COLL_ATTR_ID_COL_NAME +
") SELECT @MEDIA_ITEM_ID, " + FOREIGN_COLL_ATTR_ID_COL_NAME + " FROM " + collectionAttributeTableName +
" WHERE " + COLL_ATTR_VALUE_COL_NAME + " = @COLL_ATTR_VALUE AND NOT EXISTS(" +
"SELECT V." + FOREIGN_COLL_ATTR_ID_COL_NAME + " FROM " + collectionAttributeTableName + " V " +
" INNER JOIN " + nmTableName + " NM ON V." + FOREIGN_COLL_ATTR_ID_COL_NAME + " = NM." + FOREIGN_COLL_ATTR_ID_COL_NAME +
" WHERE V." + COLL_ATTR_VALUE_COL_NAME + " = @COLL_ATTR_VALUE AND NM." + MIA_MEDIA_ITEM_ID_COL_NAME + " = @MEDIA_ITEM_ID" +
")";
database.AddParameter(command, "MEDIA_ITEM_ID", mediaItemId, typeof(Guid)); // Used twice in query
database.AddParameter(command, "COLL_ATTR_VALUE", value, spec.AttributeType); // Used twice in query
command.ExecuteNonQuery();
}
}
protected object GetManyToOneMIAAttributeValue(ITransaction transaction, Guid mediaItemId,
MediaItemAspectMetadata.AttributeSpecification spec, string miaTableName)
{
string collectionAttributeTableName = GetMIACollectionAttributeTableName(spec);
string mainTableAttrName = GetMIAAttributeColumnName(spec);
ISQLDatabase database = transaction.Database;
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = "SELECT " + COLL_ATTR_VALUE_COL_NAME + " FROM " + collectionAttributeTableName + " V" +
" INNER JOIN " + miaTableName + " MAIN ON V." + FOREIGN_COLL_ATTR_ID_COL_NAME + " = MAIN." + mainTableAttrName +
" WHERE MAIN." + MIA_MEDIA_ITEM_ID_COL_NAME + " = @MEDIA_ITEM_ID";
database.AddParameter(command, "MEDIA_ITEM_ID", mediaItemId, typeof(Guid));
Type valueType = spec.AttributeType;
using (IDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow))
{
if (reader.Read())
return database.ReadDBValue(valueType, reader, 0);
return null;
}
}
}
protected object ReadObject(ISQLDatabase database, IDataReader reader, int colIndex, MediaItemAspectMetadata.AttributeSpecification spec)
{
// Because the IDataReader interface doesn't provide a getter method which takes the desired return type,
// we have to write this method
Type type = spec.AttributeType;
try
{
return database.ReadDBValue(type, reader, colIndex);
}
catch (ArgumentException)
{
throw new NotSupportedException(string.Format(
"The datatype '{0}' of attribute '{1}' in media item aspect type '{2}' (id '{3}') is not supported", type, spec.AttributeName, spec.ParentMIAM.Name, spec.ParentMIAM.AspectId));
}
}
protected IList GetManyToManyMIAAttributeValues(ITransaction transaction, Guid mediaItemId,
MediaItemAspectMetadata.AttributeSpecification spec)
{
string collectionAttributeTableName = GetMIACollectionAttributeTableName(spec);
string nmTableName = GenerateMIACollectionAttributeNMTableName(transaction, spec);
ISQLDatabase database = transaction.Database;
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = "SELECT " + COLL_ATTR_VALUE_COL_NAME + " FROM " + collectionAttributeTableName + " V" +
" INNER JOIN " + nmTableName + " NM ON V." + FOREIGN_COLL_ATTR_ID_COL_NAME + " = NM." + FOREIGN_COLL_ATTR_ID_COL_NAME +
" WHERE NM." + MIA_MEDIA_ITEM_ID_COL_NAME + " = @MEDIA_ITEM_ID";
database.AddParameter(command, "MEDIA_ITEM_ID", mediaItemId, typeof(Guid));
Type valueType = spec.AttributeType;
using (IDataReader reader = command.ExecuteReader())
{
IList result = new ArrayList();
while (reader.Read())
result.Add(database.ReadDBValue(valueType, reader, 0));
return result;
}
}
}
protected void LockAttribute(MediaItemAspectMetadata.AttributeSpecification spec)
{
lock (_syncObj)
{
Thread currentThread = Thread.CurrentThread;
ThreadOwnership to;
while (_lockedAttrs.TryGetValue(spec, out to) && to.CurrentThread != currentThread)
Monitor.Wait(_syncObj);
if (!_lockedAttrs.TryGetValue(spec, out to))
_lockedAttrs[spec] = to = new ThreadOwnership(currentThread);
to.LockCount++;
}
}
protected void UnlockAttribute(MediaItemAspectMetadata.AttributeSpecification spec)
{
lock (_syncObj)
{
Thread currentThread = Thread.CurrentThread;
ThreadOwnership to;
if (!_lockedAttrs.TryGetValue(spec, out to) || to.CurrentThread != currentThread)
throw new IllegalCallException("Media item aspect attribute '{0}' of media item aspect '{1}' (id '{2}') is not locked by the current thread",
spec.AttributeName, spec.ParentMIAM.Name, spec.ParentMIAM.AspectId);
to.LockCount--;
if (to.LockCount == 0)
{
_lockedAttrs.Remove(spec);
Monitor.PulseAll(_syncObj);
}
}
}
/// <summary>
/// Creates a table query of the external table of an attribute of cardinality <see cref="Cardinality.ManyToOne"/>.
/// </summary>
/// <param name="miaManagement">MIA management instance.</param>
/// <param name="spec">Attribute type of cardinality <see cref="Cardinality.ManyToOne"/> whose table should be requested.</param>
/// <returns>Table query for the table of the given attribute type.</returns>
public static TableQueryData CreateTableQueryOfMTOTable(MIA_Management miaManagement,
MediaItemAspectMetadata.AttributeSpecification spec)
{
return new TableQueryData(miaManagement.GetMIACollectionAttributeTableName(spec));
}
protected void InsertOrUpdateOneToManyMIAAttributeValues(ITransaction transaction,
MediaItemAspectMetadata.AttributeSpecification spec, Guid mediaItemId, IEnumerable values, bool insert)
{
string collectionAttributeTableName = GetMIACollectionAttributeTableName(spec);
if (!insert)
// Delete old entries
DeleteOneToManyAttributeValuesNotInEnumeration(transaction, spec, mediaItemId, values);
ISQLDatabase database = transaction.Database;
IDatabaseManager databaseManager = ServiceRegistration.Get<IDatabaseManager>();
// Add new entries - commands for insert and update are the same here
foreach (object value in values)
{
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = "INSERT INTO " + collectionAttributeTableName + "(" +
MIA_MEDIA_ITEM_ID_COL_NAME + ", " + COLL_ATTR_VALUE_COL_NAME + ") SELECT @MEDIA_ITEM_ID, @COLL_ATTR_VALUE FROM " +
databaseManager.DummyTableName +
" WHERE NOT EXISTS(SELECT " + MIA_MEDIA_ITEM_ID_COL_NAME + " FROM " + collectionAttributeTableName + " WHERE " +
MIA_MEDIA_ITEM_ID_COL_NAME + " = @MEDIA_ITEM_ID AND " + COLL_ATTR_VALUE_COL_NAME + " = @COLL_ATTR_VALUE)";
database.AddParameter(command, "MEDIA_ITEM_ID", mediaItemId, typeof(Guid)); // Used twice in query
object writeValue = TruncateBigValue(value, spec);
database.AddParameter(command, "COLL_ATTR_VALUE", writeValue, spec.AttributeType); // Used twice in query
command.ExecuteNonQuery();
}
}
}
public void RegisterLocallyKnownMediaItemAspectType(MediaItemAspectMetadata miam)
{
IMediaLibrary mediaLibrary = ServiceRegistration.Get<IMediaLibrary>();
mediaLibrary.AddMediaItemAspectStorage(miam);
}
protected void CleanupAllManyToOneOrphanedAttributeValues(ITransaction transaction, MediaItemAspectMetadata miaType)
{
foreach (MediaItemAspectMetadata.AttributeSpecification spec in miaType.AttributeSpecifications.Values)
switch (spec.Cardinality)
{
case Cardinality.Inline:
case Cardinality.OneToMany:
break;
case Cardinality.ManyToOne:
CleanupManyToOneOrphanedAttributeValues(transaction, spec);
break;
case Cardinality.ManyToMany:
break;
default:
throw new NotImplementedException(string.Format("Cardinality '{0}' for attribute '{1}.{2}' is not implemented",
spec.Cardinality, spec.ParentMIAM.AspectId, spec.AttributeName));
}
}
public static CompiledGroupedAttributeValueQuery Compile(MIA_Management miaManagement,
IEnumerable<Guid> necessaryRequestedMIATypeIDs,
MediaItemAspectMetadata.AttributeSpecification selectAttribute, IAttributeFilter selectAttributeFilter,
SelectProjectionFunction selectProjectionFunction, Type projectionValueType, IFilter filter)
{
IDictionary<Guid, MediaItemAspectMetadata> availableMIATypes = miaManagement.ManagedMediaItemAspectTypes;
// If we're doing a complex query, we can optimize if we have an extra select attribute filter, i.e. a restriction
// on the result set of values. See ComplexAttributeQueryBuilder.GenerateSqlGroupByStatement().
bool simpleQuery = selectAttribute.Cardinality == Cardinality.Inline || selectAttribute.Cardinality == Cardinality.ManyToOne;
IFilter combinedFilter = simpleQuery ?
BooleanCombinationFilter.CombineFilters(BooleanOperator.And, new IFilter[] {filter, selectAttributeFilter}) : filter;
selectAttributeFilter = simpleQuery ? null : selectAttributeFilter;
ICollection<MediaItemAspectMetadata> necessaryMIATypes = new List<MediaItemAspectMetadata>();
// Raise exception if necessary MIA types are not present
foreach (Guid miaTypeID in necessaryRequestedMIATypeIDs)
{
MediaItemAspectMetadata miam;
if (!availableMIATypes.TryGetValue(miaTypeID, out miam))
throw new InvalidDataException("Necessary requested MIA type of ID '{0}' is not present in the media library", miaTypeID);
necessaryMIATypes.Add(miam);
}
return new CompiledGroupedAttributeValueQuery(miaManagement, necessaryMIATypes, selectAttribute, selectAttributeFilter,
selectProjectionFunction, projectionValueType, combinedFilter);
}
protected void CleanupManyToOneOrphanedAttributeValues(ITransaction transaction,
MediaItemAspectMetadata.AttributeSpecification spec)
{
string collectionAttributeTableName = GetMIACollectionAttributeTableName(spec);
string miaTableName = GetMIATableName(spec.ParentMIAM);
string attrColName = GetMIAAttributeColumnName(spec);
using (IDbCommand command = transaction.CreateCommand())
{
command.CommandText = "DELETE FROM " + collectionAttributeTableName + " WHERE NOT EXISTS (" +
"SELECT " + MIA_MEDIA_ITEM_ID_COL_NAME + " FROM " + miaTableName + " MIA WHERE MIA." +
attrColName + " = " + collectionAttributeTableName + "." + FOREIGN_COLL_ATTR_ID_COL_NAME + ")";
command.ExecuteNonQuery();
}
}
public SimpleMLFilterCriterion(MediaItemAspectMetadata.AttributeSpecification attributeType)
{
_attributeType = attributeType;
}
/// <summary>
/// Convenience method to get a <see cref="MediaItemAspect"/> from the given <paramref name="aspects"/> dictionary or add a new instance to the
/// dictionary and return it.
/// </summary>
/// <param name="aspects">Dictionary of MediaItemAspects.</param>
/// <param name="mediaItemAspectMetadata">Definiton of metadata that is used for creation.</param>
/// <returns>Existing or new <see cref="MediaItemAspect"/> instance.</returns>
public static MediaItemAspect GetOrCreateAspect(IDictionary <Guid, MediaItemAspect> aspects, MediaItemAspectMetadata mediaItemAspectMetadata)
{
MediaItemAspect mediaAspect;
Guid aspectId = mediaItemAspectMetadata.AspectId;
if (!aspects.TryGetValue(aspectId, out mediaAspect))
{
aspects[aspectId] = mediaAspect = new MediaItemAspect(mediaItemAspectMetadata);
}
return(mediaAspect);
}