/// <summary>
/// Returns a dictionary which maps the system ids of all attached clients to their last hostname.
/// </summary>
/// <returns>Dictionary with system ids mapped to host names.</returns>
protected IDictionary <string, MPClientMetadata> ReadAttachedClientsFromDB()
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
int systemIdIndex;
int lastHostNameIndex;
int lastClientNameIndex;
IDictionary <string, MPClientMetadata> result = new Dictionary <string, MPClientMetadata>();
using (IDbCommand command = ClientManager_SubSchema.SelectAttachedClientsCommand(transaction, out systemIdIndex,
out lastHostNameIndex, out lastClientNameIndex))
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string clientSystemId = database.ReadDBValue <string>(reader, systemIdIndex);
string lastClientHostName = database.ReadDBValue <string>(reader, lastHostNameIndex);
SystemName lastHostName = lastClientHostName == null ? null : new SystemName(lastClientHostName);
string lastClientName = database.ReadDBValue <string>(reader, lastClientNameIndex);
result.Add(clientSystemId, new MPClientMetadata(clientSystemId, lastHostName, lastClientName));
}
}
return(result);
}
finally
{
transaction.Dispose();
}
}
protected ICollection <UserProfile> GetProfiles(Guid?profileId, string name)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
int profileIdIndex;
int nameIndex;
using (IDbCommand command = UserProfileDataManagement_SubSchema.SelectUserProfilesCommand(transaction, profileId, name,
out profileIdIndex, out nameIndex))
{
ICollection <UserProfile> result = new List <UserProfile>();
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Guid profileId_ = database.ReadDBValue <Guid>(reader, profileIdIndex);
string name_ = database.ReadDBValue <string>(reader, nameIndex);
result.Add(new UserProfile(profileId_, name_));
}
}
return(result);
}
}
finally
{
transaction.Dispose();
}
}
public HomogenousMap Execute()
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
using (IDbCommand command = transaction.CreateCommand())
{
string valueAlias;
string groupSizeAlias;
string statementStr;
IList <BindVar> bindVars;
if (_selectAttribute.Cardinality == Cardinality.Inline || _selectAttribute.Cardinality == Cardinality.ManyToOne)
{
QueryAttribute selectAttributeQA = new QueryAttribute(_selectAttribute);
MainQueryBuilder builder = new MainQueryBuilder(_miaManagement,
new QueryAttribute[] { selectAttributeQA }, _selectProjectionFunction,
_necessaryRequestedMIATypes, new MediaItemAspectMetadata[] {}, _filter, null);
IDictionary <QueryAttribute, string> qa2a;
builder.GenerateSqlGroupByStatement(out groupSizeAlias, out qa2a, out statementStr, out bindVars);
valueAlias = qa2a[selectAttributeQA];
}
else
{
ComplexAttributeQueryBuilder builder = new ComplexAttributeQueryBuilder(_miaManagement, _selectAttribute,
_selectProjectionFunction, _necessaryRequestedMIATypes, _filter);
builder.GenerateSqlGroupByStatement(_selectAttributeFilter, out valueAlias, out groupSizeAlias,
out statementStr, out bindVars);
}
command.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
{
database.AddParameter(command, bindVar.Name, bindVar.Value, bindVar.VariableType);
}
Type valueType = _projectionValueType ?? _selectAttribute.AttributeType;
HomogenousMap result = new HomogenousMap(valueType, typeof(int));
using (IDataReader reader = command.ExecuteReader())
{
int valueCol = reader.GetOrdinal(valueAlias);
int groupSizeCol = reader.GetOrdinal(groupSizeAlias);
while (reader.Read())
{
result.Add(database.ReadDBValue(valueType, reader, valueCol),
database.ReadDBValue <int>(reader, groupSizeCol));
}
}
return(result);
}
}
finally
{
transaction.Dispose();
}
}
private void AddComplexAttributes(ISQLDatabase database, ITransaction transaction, IEnumerable <Guid> ids, IDictionary <Guid, IDictionary <MediaItemAspectMetadata.AttributeSpecification, IList> > complexAttributeValues, IDictionary <Guid, IList <Guid> > complexMediaItemIds)
{
string statementStr;
IList <BindVar> bindVars;
foreach (MediaItemAspectMetadata.AttributeSpecification attr in _explicitSelectAttributes)
{
// Skip this attribute if no media items were found for the aspect
if (!complexMediaItemIds.ContainsKey(attr.ParentMIAM.AspectId))
{
continue;
}
ComplexAttributeQueryBuilder builder = new ComplexAttributeQueryBuilder(
_miaManagement, attr, null, _necessaryRequestedMIAs, new MediaItemIdFilter(ids), _subqueryFilter);
using (IDbCommand command = transaction.CreateCommand())
{
string mediaItemIdAlias;
string valueAlias;
builder.GenerateSqlStatement(out mediaItemIdAlias, out valueAlias,
out statementStr, out bindVars);
command.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
{
database.AddParameter(command, bindVar.Name, bindVar.Value, bindVar.VariableType);
}
Type valueType = attr.AttributeType;
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Guid mediaItemId = database.ReadDBValue <Guid>(reader, reader.GetOrdinal(mediaItemIdAlias));
object value = database.ReadDBValue(valueType, reader, reader.GetOrdinal(valueAlias));
IDictionary <MediaItemAspectMetadata.AttributeSpecification, IList> attributeValues;
if (!complexAttributeValues.TryGetValue(mediaItemId, out attributeValues))
{
attributeValues = complexAttributeValues[mediaItemId] =
new Dictionary <MediaItemAspectMetadata.AttributeSpecification, IList>();
}
IList attrValues;
if (!attributeValues.TryGetValue(attr, out attrValues))
{
attrValues = attributeValues[attr] = CreateGenericList(attr.AttributeType);
}
attrValues.Add(value);
}
}
}
}
}
public bool GetUserAdditionalData(Guid profileId, string key, out string data)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
int dataIndex;
using (IDbCommand command = UserProfileDataManagement_SubSchema.SelectUserAdditionalDataCommand(transaction, profileId,
key, out dataIndex))
{
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
data = database.ReadDBValue <string>(reader, dataIndex);
return(true);
}
}
}
data = null;
return(false);
}
finally
{
transaction.Dispose();
}
}
public Task <AsyncResult <string> > GetUserAdditionalDataAsync(Guid profileId, string key, int dataNo = 0)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
int dataIndex;
using (IDbCommand command = UserProfileDataManagement_SubSchema.SelectUserAdditionalDataCommand(transaction, profileId,
key, dataNo, out dataIndex))
{
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
var data = database.ReadDBValue <string>(reader, dataIndex);
return(Task.FromResult(new AsyncResult <string>(true, data)));
}
}
}
return(Task.FromResult(new AsyncResult <string>(false, null)));
}
finally
{
transaction.Dispose();
}
}
public Task <AsyncResult <IEnumerable <Tuple <string, int, string> > > > GetUserSelectedAdditionalDataListAsync(Guid profileId, string[] keys, bool sortByKey = false, SortDirection sortDirection = SortDirection.Ascending,
uint?offset = null, uint?limit = null)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
int dataNoIndex;
int dataIndex;
int keyIndex;
List <Tuple <string, int, string> > list = new List <Tuple <string, int, string> >();
using (IDbCommand command = UserProfileDataManagement_SubSchema.SelectUserAdditionalDataListCommand(transaction, profileId,
keys, sortByKey, sortDirection, out keyIndex, out dataNoIndex, out dataIndex))
{
using (IDataReader reader = command.ExecuteReader())
{
var records = reader.AsEnumerable();
if (offset.HasValue)
{
records = records.Skip((int)offset.Value);
}
if (limit.HasValue)
{
records = records.Take((int)limit.Value);
}
foreach (var record in records)
{
list.Add(new Tuple <string, int, string>(database.ReadDBValue <string>(record, keyIndex), database.ReadDBValue <int>(record, dataNoIndex),
database.ReadDBValue <string>(record, dataIndex)));
}
}
}
IEnumerable <Tuple <string, int, string> > data = null;
if (list.Count > 0)
{
data = list;
}
return(Task.FromResult(new AsyncResult <IEnumerable <Tuple <string, int, string> > >(data != null, data)));
}
finally
{
transaction.Dispose();
}
}
public Task <bool> ChangeProfileIdAsync(Guid profileId, Guid newProfileId)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
bool result;
int nameIndex;
string profileName;
using (IDbCommand command = UserProfileDataManagement_SubSchema.SelectUserProfileNameCommand(transaction, profileId, out nameIndex))
{
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
profileName = database.ReadDBValue <string>(reader, nameIndex);
}
else
{
transaction.Rollback();
return(Task.FromResult(false));
}
}
}
using (IDbCommand command = UserProfileDataManagement_SubSchema.UpdateUserProfileNameCommand(transaction, profileId, profileName + "_old"))
command.ExecuteNonQuery();
using (IDbCommand command = UserProfileDataManagement_SubSchema.CopyUserProfileCommand(transaction, profileId, newProfileId, profileName))
result = command.ExecuteNonQuery() > 0;
using (IDbCommand command = UserProfileDataManagement_SubSchema.CopyUserMediaItemDataCommand(transaction, profileId, newProfileId))
command.ExecuteNonQuery();
using (IDbCommand command = UserProfileDataManagement_SubSchema.CopyUserPlaylistDataCommand(transaction, profileId, newProfileId))
command.ExecuteNonQuery();
using (IDbCommand command = UserProfileDataManagement_SubSchema.CopyUserAdditionalDataCommand(transaction, profileId, newProfileId))
command.ExecuteNonQuery();
using (IDbCommand command = UserProfileDataManagement_SubSchema.DeleteUserProfileCommand(transaction, profileId))
command.ExecuteNonQuery();
transaction.Commit();
return(Task.FromResult(result));
}
catch (Exception e)
{
ServiceRegistration.Get <ILogger>().Error("UserProfileDataManagement: Error changing profile Id '{0}'", e, profileId);
transaction.Rollback();
throw;
}
}
/// <summary>
/// Gets all media item ids from the database.
/// </summary>
/// <returns></returns>
protected ICollection <Guid> GetAllMediaItemIds()
{
HashSet <Guid> mediaItemIds = new HashSet <Guid>();
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
using (ITransaction transaction = database.BeginTransaction())
using (IDbCommand command = MediaLibrary_SubSchema.SelectAllMediaItemIdsCommand(transaction))
using (var reader = command.ExecuteReader())
while (reader.Read())
{
mediaItemIds.Add(database.ReadDBValue <Guid>(reader, 0));
}
return(mediaItemIds);
}
public ICollection <string> GetDatabaseSubSchemas()
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>(false);
using (ITransaction transaction = database.BeginTransaction())
{
int nameIndex;
using (IDbCommand command = MediaPortal_Basis_Schema.SelectAllSubSchemaNames(transaction, out nameIndex))
using (IDataReader reader = command.ExecuteReader())
{
ICollection <string> result = new List <string>();
while (reader.Read())
{
result.Add(database.ReadDBValue <string>(reader, nameIndex));
}
return(result);
}
}
}
private IList <MediaItem> GetMediaItems(ISQLDatabase database, ITransaction transaction, bool singleMode, IEnumerable <MediaItemAspectMetadata> selectedMIAs, out IList <Guid> mediaItemIds, out IDictionary <Guid, IList <Guid> > complexMediaItems)
{
string statementStr;
IList <BindVar> bindVars;
MIAQueryBuilder builder = new MIAQueryBuilder(_miaManagement,
_mainSelectAttributes.Values, null, _necessaryRequestedMIAs, _optionalRequestedMIAs, _filter, _subqueryFilter, _sortInformation);
using (IDbCommand command = transaction.CreateCommand())
{
string mediaItemIdAlias2;
IDictionary <MediaItemAspectMetadata, string> miamAliases;
// Maps (selected and filtered) QueryAttributes to CompiledQueryAttributes in the SQL query
IDictionary <QueryAttribute, string> qa2a;
builder.GenerateSqlStatement(out mediaItemIdAlias2, out miamAliases, out qa2a,
out statementStr, out bindVars);
// Try to use SQL side paging, which gives best performance if supported
ISQLDatabasePaging paging = database as ISQLDatabasePaging;
if (paging != null)
{
paging.Process(ref statementStr, ref bindVars, ref _offset, ref _limit);
}
command.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
{
database.AddParameter(command, bindVar.Name, bindVar.Value, bindVar.VariableType);
}
using (IDataReader fullReader = command.ExecuteReader())
{
IList <MediaItem> result = new List <MediaItem>();
mediaItemIds = new List <Guid>();
complexMediaItems = new Dictionary <Guid, IList <Guid> >();
var records = fullReader.AsEnumerable();
if (_offset.HasValue)
{
records = records.Skip((int)_offset.Value);
}
if (_limit.HasValue)
{
records = records.Take((int)_limit.Value);
}
foreach (var reader in records)
{
Guid mediaItemId = database.ReadDBValue <Guid>(reader, reader.GetOrdinal(mediaItemIdAlias2));
if (mediaItemIds.Contains(mediaItemId))
{
// Media item was already added to result - query results are not always unique because of JOINs used for filtering
continue;
}
mediaItemIds.Add(mediaItemId);
MediaItem mediaItem = new MediaItem(mediaItemId);
foreach (SingleMediaItemAspectMetadata miam in selectedMIAs.Where(x => x is SingleMediaItemAspectMetadata))
{
string name;
if (!miamAliases.TryGetValue(miam, out name) || reader.IsDBNull(reader.GetOrdinal(name)))
{
// MIAM is not available for current media item
continue;
}
IList <Guid> complexIds;
if (!complexMediaItems.TryGetValue(miam.AspectId, out complexIds))
{
complexMediaItems[miam.AspectId] = complexIds = new List <Guid>();
}
complexIds.Add(mediaItemId);
SingleMediaItemAspect mia = new SingleMediaItemAspect(miam);
foreach (MediaItemAspectMetadata.AttributeSpecification attr in miam.AttributeSpecifications.Values)
{
if (attr.Cardinality == Cardinality.Inline)
{
QueryAttribute qa = _mainSelectAttributes[attr];
string alias = qa2a[qa];
mia.SetAttribute(attr, database.ReadDBValue(attr.AttributeType, reader, reader.GetOrdinal(alias)));
}
}
MediaItemAspect.SetAspect(mediaItem.Aspects, mia);
}
result.Add(mediaItem);
if (singleMode)
{
break;
}
}
return(result);
}
}
}
public MediaItem QueryMediaItem()
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
MediaItem result = null;
// 1. Main query
MainQueryBuilder mainQueryBuilder = new MainQueryBuilder(_miaManagement,
_mainSelectAttributes.Values, null, _necessaryRequestedMIAs, _optionalRequestedMIAs, _filter, _sortInformation);
using (IDbCommand mainQueryCommand = transaction.CreateCommand())
{
string mediaItemIdAlias2;
IDictionary <MediaItemAspectMetadata, string> miamAliases;
// Maps (selected and filtered) QueryAttributes to CompiledQueryAttributes in the SQL query
IDictionary <QueryAttribute, string> qa2a;
string statementStr;
IList <BindVar> bindVars;
mainQueryBuilder.GenerateSqlStatement(out mediaItemIdAlias2, out miamAliases, out qa2a,
out statementStr, out bindVars);
mainQueryCommand.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
{
database.AddParameter(mainQueryCommand, bindVar.Name, bindVar.Value, bindVar.VariableType);
}
IEnumerable <MediaItemAspectMetadata> selectedMIAs = _necessaryRequestedMIAs.Union(_optionalRequestedMIAs);
using (IDataReader mainReader = mainQueryCommand.ExecuteReader())
{
if (mainReader.Read())
{
Guid mediaItemId = database.ReadDBValue <Guid>(mainReader, mainReader.GetOrdinal(mediaItemIdAlias2));
result = new MediaItem(mediaItemId);
// Request complex attributes using media item ID
IFilter modifiedFilter = BooleanCombinationFilter.CombineFilters(BooleanOperator.And, _filter, new MediaItemIdFilter(mediaItemId));
IDictionary <MediaItemAspectMetadata.AttributeSpecification, ICollection <object> > complexAttributeValues =
new Dictionary <MediaItemAspectMetadata.AttributeSpecification, ICollection <object> >();
foreach (MediaItemAspectMetadata.AttributeSpecification attr in _explicitSelectAttributes)
{
ComplexAttributeQueryBuilder complexAttributeQueryBuilder = new ComplexAttributeQueryBuilder(
_miaManagement, attr, null, null, modifiedFilter);
using (IDbCommand complexQueryCommand = transaction.CreateCommand())
{
string mediaItemIdAlias;
string valueAlias;
complexAttributeQueryBuilder.GenerateSqlStatement(out mediaItemIdAlias, out valueAlias,
out statementStr, out bindVars);
complexQueryCommand.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
{
database.AddParameter(complexQueryCommand, bindVar.Name, bindVar.Value, bindVar.VariableType);
}
Type valueType = attr.AttributeType;
using (IDataReader reader = complexQueryCommand.ExecuteReader())
{
if (reader.Read())
{
object value = database.ReadDBValue(valueType, reader, reader.GetOrdinal(valueAlias));
ICollection <object> attrValues;
if (!complexAttributeValues.TryGetValue(attr, out attrValues))
{
attrValues = complexAttributeValues[attr] = new List <object>();
}
attrValues.Add(value);
}
}
}
}
// Put together all attributes
foreach (MediaItemAspectMetadata miam in selectedMIAs)
{
if (mainReader.IsDBNull(mainReader.GetOrdinal(miamAliases[miam])))
{
// MIAM is not available for current media item
continue;
}
MediaItemAspect mia = new MediaItemAspect(miam);
foreach (MediaItemAspectMetadata.AttributeSpecification attr in miam.AttributeSpecifications.Values)
{
if (attr.Cardinality == Cardinality.Inline)
{
QueryAttribute qa = _mainSelectAttributes[attr];
string alias = qa2a[qa];
mia.SetAttribute(attr, database.ReadDBValue(attr.AttributeType, mainReader, mainReader.GetOrdinal(alias)));
}
else
{
ICollection <object> attrValues;
if (complexAttributeValues.TryGetValue(attr, out attrValues))
{
mia.SetCollectionAttribute(attr, attrValues);
}
}
}
result.Aspects[miam.AspectId] = mia;
}
}
return(result);
}
}
}
finally
{
transaction.Dispose();
}
}
public IList <MediaItem> QueryList()
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
string statementStr;
IList <BindVar> bindVars;
// 1. Request all complex attributes
IDictionary <Guid, IDictionary <MediaItemAspectMetadata.AttributeSpecification, ICollection <object> > > complexAttributeValues =
new Dictionary <Guid, IDictionary <MediaItemAspectMetadata.AttributeSpecification, ICollection <object> > >();
foreach (MediaItemAspectMetadata.AttributeSpecification attr in _explicitSelectAttributes)
{
ComplexAttributeQueryBuilder complexAttributeQueryBuilder = new ComplexAttributeQueryBuilder(
_miaManagement, attr, null, _necessaryRequestedMIAs, _filter);
using (IDbCommand command = transaction.CreateCommand())
{
string mediaItemIdAlias;
string valueAlias;
complexAttributeQueryBuilder.GenerateSqlStatement(out mediaItemIdAlias, out valueAlias,
out statementStr, out bindVars);
command.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
{
database.AddParameter(command, bindVar.Name, bindVar.Value, bindVar.VariableType);
}
Type valueType = attr.AttributeType;
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Guid mediaItemId = database.ReadDBValue <Guid>(reader, reader.GetOrdinal(mediaItemIdAlias));
object value = database.ReadDBValue(valueType, reader, reader.GetOrdinal(valueAlias));
IDictionary <MediaItemAspectMetadata.AttributeSpecification, ICollection <object> > attributeValues;
if (!complexAttributeValues.TryGetValue(mediaItemId, out attributeValues))
{
attributeValues = complexAttributeValues[mediaItemId] =
new Dictionary <MediaItemAspectMetadata.AttributeSpecification, ICollection <object> >();
}
ICollection <object> attrValues;
if (!attributeValues.TryGetValue(attr, out attrValues))
{
attrValues = attributeValues[attr] = new List <object>();
}
attrValues.Add(value);
}
}
}
}
// 2. Main query
MainQueryBuilder mainQueryBuilder = new MainQueryBuilder(_miaManagement,
_mainSelectAttributes.Values, null, _necessaryRequestedMIAs, _optionalRequestedMIAs, _filter, _sortInformation);
using (IDbCommand command = transaction.CreateCommand())
{
string mediaItemIdAlias2;
IDictionary <MediaItemAspectMetadata, string> miamAliases;
// Maps (selected and filtered) QueryAttributes to CompiledQueryAttributes in the SQL query
IDictionary <QueryAttribute, string> qa2a;
mainQueryBuilder.GenerateSqlStatement(out mediaItemIdAlias2, out miamAliases, out qa2a,
out statementStr, out bindVars);
command.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
{
database.AddParameter(command, bindVar.Name, bindVar.Value, bindVar.VariableType);
}
IEnumerable <MediaItemAspectMetadata> selectedMIAs = _necessaryRequestedMIAs.Union(_optionalRequestedMIAs);
ICollection <Guid> mediaItems = new HashSet <Guid>();
using (IDataReader reader = command.ExecuteReader())
{
IList <MediaItem> result = new List <MediaItem>();
while (reader.Read())
{
Guid mediaItemId = database.ReadDBValue <Guid>(reader, reader.GetOrdinal(mediaItemIdAlias2));
if (mediaItems.Contains(mediaItemId))
{
// Media item was already added to result - query results are not always unique because of JOINs used for filtering
continue;
}
mediaItems.Add(mediaItemId);
IDictionary <MediaItemAspectMetadata.AttributeSpecification, ICollection <object> > attributeValues;
if (!complexAttributeValues.TryGetValue(mediaItemId, out attributeValues))
{
attributeValues = null;
}
MediaItem mediaItem = new MediaItem(mediaItemId);
foreach (MediaItemAspectMetadata miam in selectedMIAs)
{
if (reader.IsDBNull(reader.GetOrdinal(miamAliases[miam])))
{
// MIAM is not available for current media item
continue;
}
MediaItemAspect mia = new MediaItemAspect(miam);
foreach (MediaItemAspectMetadata.AttributeSpecification attr in miam.AttributeSpecifications.Values)
{
if (attr.Cardinality == Cardinality.Inline)
{
QueryAttribute qa = _mainSelectAttributes[attr];
string alias = qa2a[qa];
mia.SetAttribute(attr, database.ReadDBValue(attr.AttributeType, reader, reader.GetOrdinal(alias)));
}
else
{
ICollection <object> attrValues;
if (attributeValues != null && attributeValues.TryGetValue(attr, out attrValues))
{
mia.SetCollectionAttribute(attr, attrValues);
}
}
}
mediaItem.Aspects[miam.AspectId] = mia;
}
result.Add(mediaItem);
}
return(result);
}
}
}
finally
{
transaction.Dispose();
}
}
/// <summary>
/// Reads a value of the given type from the given database <paramref name="reader"/> from the column of the given <paramref name="colIndex"/>.
/// </summary>
/// <typeparam name="T">Type of the parameter to read. The data access layer will automatically try to convert a value of a convertible
/// format, if possible.</typeparam>
/// <param name="database">Underlaying SQL database.</param>
/// <param name="reader">Reader containing the value to read.</param>
/// <param name="colIndex">Index of the column to read. Indices start at <c>0</c>.</param>
/// <returns>Value which was read or <c>null</c>.</returns>
public static T ReadDBValue <T>(this ISQLDatabase database, IDataReader reader, int colIndex)
{
return((T)database.ReadDBValue(typeof(T), reader, colIndex));
}
//TODO: DbCommand Async call?
protected Task <ICollection <UserProfile> > GetProfiles(Guid?profileId, string name, bool loadData = true)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
int profileIdIndex;
int nameIndex;
int idIndex;
int dataIndex;
int lastLoginIndex;
int imageIndex;
ICollection <UserProfile> result = new List <UserProfile>();
using (IDbCommand command = UserProfileDataManagement_SubSchema.SelectUserProfilesCommand(transaction, profileId, name,
out profileIdIndex, out nameIndex, out idIndex, out dataIndex, out lastLoginIndex, out imageIndex))
{
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
result.Add(new UserProfile(
database.ReadDBValue <Guid>(reader, profileIdIndex),
database.ReadDBValue <string>(reader, nameIndex),
(UserProfileType)database.ReadDBValue <int>(reader, idIndex),
database.ReadDBValue <string>(reader, dataIndex),
database.ReadDBValue <DateTime?>(reader, lastLoginIndex),
database.ReadDBValue <byte[]>(reader, imageIndex))
);
}
}
}
if (loadData)
{
foreach (var user in result)
{
using (IDbCommand command = UserProfileDataManagement_SubSchema.SelectUserAdditionalDataListCommand(transaction, user.ProfileId, null, false, SortDirection.Ascending,
out nameIndex, out profileIdIndex, out dataIndex))
{
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string key = database.ReadDBValue <string>(reader, nameIndex);
if (!user.AdditionalData.ContainsKey(key))
{
user.AdditionalData.Add(key, new Dictionary <int, string>());
}
user.AdditionalData[key].Add(database.ReadDBValue <int>(reader, profileIdIndex), database.ReadDBValue <string>(reader, dataIndex));
}
}
}
}
}
return(Task.FromResult(result));
}
finally
{
transaction.Dispose();
}
}
private void AddMultipleMIAResults(ISQLDatabase database, ITransaction transaction, MultipleMediaItemAspectMetadata miam,
IDictionary <MediaItemAspectMetadata.AttributeSpecification, QueryAttribute> attributes, MainQueryBuilder builder,
IDictionary <Guid, ICollection <MultipleMediaItemAspect> > multipleMiaValues)
{
if (miam.IsTransientAspect)
{
return;
}
using (IDbCommand command = transaction.CreateCommand())
{
string mediaItemIdAlias;
IDictionary <MediaItemAspectMetadata, string> miamAliases;
// Maps (selected and filtered) QueryAttributes to CompiledQueryAttributes in the SQL query
IDictionary <QueryAttribute, string> qa2a;
string statementStr;
IList <BindVar> bindVars;
builder.GenerateSqlStatement(out mediaItemIdAlias, out miamAliases, out qa2a, out statementStr, out bindVars);
command.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
{
database.AddParameter(command, bindVar.Name, bindVar.Value, bindVar.VariableType);
}
//logger.Debug("Get multiple MIAs for {0}", string.Join(",", ids));
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Guid itemId = database.ReadDBValue <Guid>(reader, reader.GetOrdinal(mediaItemIdAlias));
//logger.Debug("Read record for {0}", itemId);
MultipleMediaItemAspect mia = new MultipleMediaItemAspect(miam);
foreach (MediaItemAspectMetadata.AttributeSpecification attr in miam.AttributeSpecifications.Values)
{
if (attr.Cardinality == Cardinality.Inline)
{
QueryAttribute qa = attributes[attr];
string alias = qa2a[qa];
//logger.Debug("Reading multiple MIA attibute " + attr.AttributeName + " #" + index + " from column " + alias);
mia.SetAttribute(attr, database.ReadDBValue(attr.AttributeType, reader, reader.GetOrdinal(alias)));
}
}
if (builder is InverseRelationshipQueryBuilder)
{
/*
* Swap the ID / role <--> linked role / linked ID
* "A is a movie starting actor B"
* becomes
* "B is an actor starting in movie A"
*/
Guid id = itemId;
Guid role = mia.GetAttributeValue <Guid>(RelationshipAspect.ATTR_ROLE);
itemId = mia.GetAttributeValue <Guid>(RelationshipAspect.ATTR_LINKED_ID);
mia.SetAttribute(RelationshipAspect.ATTR_ROLE, mia.GetAttributeValue <Guid>(RelationshipAspect.ATTR_LINKED_ROLE));
mia.SetAttribute(RelationshipAspect.ATTR_LINKED_ROLE, role);
mia.SetAttribute(RelationshipAspect.ATTR_LINKED_ID, id);
}
ICollection <MultipleMediaItemAspect> values;
if (!multipleMiaValues.TryGetValue(itemId, out values))
{
values = new List <MultipleMediaItemAspect>();
multipleMiaValues[itemId] = values;
}
values.Add(mia);
}
}
}
}
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));
}
}
public Tuple <HomogenousMap, HomogenousMap> Execute()
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
using (IDbCommand command = transaction.CreateCommand())
{
string keyAlias = null;
string valueAlias = null;
string groupSizeAlias = null;
string statementStr = null;
IList <BindVar> bindVars = null;
if (_selectValueAttribute.Cardinality == Cardinality.Inline || _selectValueAttribute.Cardinality == Cardinality.ManyToOne)
{
List <QueryAttribute> qAttributes = new List <QueryAttribute>();
QueryAttribute selectValueAttributeQA = new QueryAttribute(_selectValueAttribute);
qAttributes.Add(selectValueAttributeQA);
QueryAttribute selectKeyAttributeQA = null;
if (_selectKeyAttribute != null)
{
selectKeyAttributeQA = new QueryAttribute(_selectKeyAttribute);
qAttributes.Add(selectKeyAttributeQA);
}
MIAQueryBuilder builder = new MIAQueryBuilder(_miaManagement, qAttributes, _selectProjectionFunction,
_necessaryRequestedMIATypes, new MediaItemAspectMetadata[] {}, _filter, _subqueryFilter, null);
IDictionary <QueryAttribute, string> qa2a;
builder.GenerateSqlGroupByStatement(out groupSizeAlias, out qa2a, out statementStr, out bindVars);
valueAlias = qa2a[selectValueAttributeQA];
if (_selectKeyAttribute != null)
{
keyAlias = qa2a[selectKeyAttributeQA];
}
}
else
{
if (_selectKeyAttribute != null)
{
throw new InvalidDataException("Value attribute '{0}' does not support key value grouping", _selectValueAttribute.AttributeName);
}
ComplexAttributeQueryBuilder builder = new ComplexAttributeQueryBuilder(_miaManagement, _selectValueAttribute,
_selectProjectionFunction, _necessaryRequestedMIATypes, _filter, _subqueryFilter);
builder.GenerateSqlGroupByStatement(_selectAttributeFilter, out valueAlias, out groupSizeAlias,
out statementStr, out bindVars);
}
command.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
{
database.AddParameter(command, bindVar.Name, bindVar.Value, bindVar.VariableType);
}
Tuple <HomogenousMap, HomogenousMap> result = null;
if (_selectKeyAttribute != null)
{
Type valueType = _projectionValueType ?? _selectValueAttribute.AttributeType;
Type keyType = _selectKeyAttribute.AttributeType;
HomogenousMap valueMap = new HomogenousMap(valueType, typeof(int));
HomogenousMap keyMap = new HomogenousMap(valueType, keyType);
using (IDataReader reader = command.ExecuteReader())
{
int keyCol = reader.GetOrdinal(keyAlias);
int valueCol = reader.GetOrdinal(valueAlias);
int groupSizeCol = reader.GetOrdinal(groupSizeAlias);
while (reader.Read())
{
if (!keyMap.ContainsKey(database.ReadDBValue(valueType, reader, valueCol)))
{
keyMap.Add(database.ReadDBValue(valueType, reader, valueCol), database.ReadDBValue(keyType, reader, keyCol));
valueMap.Add(database.ReadDBValue(valueType, reader, valueCol), database.ReadDBValue <int>(reader, groupSizeCol));
}
}
}
result = new Tuple <HomogenousMap, HomogenousMap>(valueMap, keyMap);
}
else
{
Type valueType = _projectionValueType ?? _selectValueAttribute.AttributeType;
HomogenousMap valueMap = new HomogenousMap(valueType, typeof(int));
using (IDataReader reader = command.ExecuteReader())
{
int valueCol = reader.GetOrdinal(valueAlias);
int groupSizeCol = reader.GetOrdinal(groupSizeAlias);
while (reader.Read())
{
valueMap.Add(database.ReadDBValue(valueType, reader, valueCol),
database.ReadDBValue <int>(reader, groupSizeCol));
}
}
result = new Tuple <HomogenousMap, HomogenousMap>(valueMap, null);
}
return(result);
}
}
finally
{
transaction.Dispose();
}
}