private string[] GetFinalColumnsToSelect(InternalItemsQuery query, string[] startColumns, IDbCommand cmd)
{
var list = startColumns.ToList();
foreach (var field in allFields)
{
if (!query.HasField(field))
{
foreach (var fieldToRemove in GetColumnNamesFromField(field).ToList())
{
list.Remove(fieldToRemove);
}
}
}
if (!query.DtoOptions.EnableImages)
{
list.Remove("Images");
}
if (EnableJoinUserData(query))
{
list.Add("UserDataDb.UserData.UserId");
list.Add("UserDataDb.UserData.lastPlayedDate");
list.Add("UserDataDb.UserData.playbackPositionTicks");
list.Add("UserDataDb.UserData.playcount");
list.Add("UserDataDb.UserData.isFavorite");
list.Add("UserDataDb.UserData.played");
list.Add("UserDataDb.UserData.rating");
}
if (query.SimilarTo != null)
{
var item = query.SimilarTo;
var builder = new StringBuilder();
builder.Append("(");
builder.Append("((OfficialRating=@ItemOfficialRating) * 10)");
//builder.Append("+ ((ProductionYear=@ItemProductionYear) * 10)");
builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 10 Then 2 Else 0 End )");
builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 5 Then 2 Else 0 End )");
//// genres
builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=2 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=2)) * 10)");
//// tags
builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=4 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=4)) * 10)");
builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=5 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=5)) * 10)");
builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=3 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=3)) * 3)");
//builder.Append("+ ((Select count(Name) from People where ItemId=Guid and Name in (select Name from People where ItemId=@SimilarItemId)) * 3)");
////builder.Append("(select group_concat((Select Name from People where ItemId=Guid and Name in (Select Name from People where ItemId=@SimilarItemId)), '|'))");
builder.Append(") as SimilarityScore");
list.Add(builder.ToString());
cmd.Parameters.Add(cmd, "@ItemOfficialRating", DbType.String).Value = item.OfficialRating;
cmd.Parameters.Add(cmd, "@ItemProductionYear", DbType.Int32).Value = item.ProductionYear ?? 0;
cmd.Parameters.Add(cmd, "@SimilarItemId", DbType.Guid).Value = item.Id;
var excludeIds = query.ExcludeItemIds.ToList();
excludeIds.Add(item.Id.ToString("N"));
query.ExcludeItemIds = excludeIds.ToArray();
query.ExcludeProviderIds = item.ProviderIds;
}
return list.ToArray();
}
private BaseItem GetItem(IDataReader reader, InternalItemsQuery query)
{
var typeString = reader.GetString(0);
var type = _typeMapper.GetType(typeString);
if (type == null)
{
//Logger.Debug("Unknown type {0}", typeString);
return null;
}
BaseItem item = null;
if (TypeRequiresDeserialization(type))
{
using (var stream = reader.GetMemoryStream(1, _memoryStreamProvider))
{
try
{
item = _jsonSerializer.DeserializeFromStream(stream, type) as BaseItem;
}
catch (SerializationException ex)
{
Logger.ErrorException("Error deserializing item", ex);
}
}
}
if (item == null)
{
try
{
item = Activator.CreateInstance(type) as BaseItem;
}
catch
{
}
}
if (item == null)
{
return null;
}
if (!reader.IsDBNull(2))
{
var hasStartDate = item as IHasStartDate;
if (hasStartDate != null)
{
hasStartDate.StartDate = reader.GetDateTime(2).ToUniversalTime();
}
}
if (!reader.IsDBNull(3))
{
item.EndDate = reader.GetDateTime(3).ToUniversalTime();
}
if (!reader.IsDBNull(4))
{
item.IsOffline = reader.GetBoolean(4);
}
if (!reader.IsDBNull(5))
{
item.ChannelId = reader.GetString(5);
}
var hasProgramAttributes = item as IHasProgramAttributes;
if (hasProgramAttributes != null)
{
if (!reader.IsDBNull(6))
{
hasProgramAttributes.IsMovie = reader.GetBoolean(6);
}
if (!reader.IsDBNull(7))
{
hasProgramAttributes.IsSports = reader.GetBoolean(7);
}
if (!reader.IsDBNull(8))
{
hasProgramAttributes.IsKids = reader.GetBoolean(8);
}
if (!reader.IsDBNull(9))
{
hasProgramAttributes.IsSeries = reader.GetBoolean(9);
}
if (!reader.IsDBNull(10))
{
hasProgramAttributes.IsLive = reader.GetBoolean(10);
}
if (!reader.IsDBNull(11))
{
hasProgramAttributes.IsNews = reader.GetBoolean(11);
}
if (!reader.IsDBNull(12))
{
hasProgramAttributes.IsPremiere = reader.GetBoolean(12);
}
if (!reader.IsDBNull(13))
{
hasProgramAttributes.EpisodeTitle = reader.GetString(13);
}
if (!reader.IsDBNull(14))
{
hasProgramAttributes.IsRepeat = reader.GetBoolean(14);
}
}
var index = 15;
if (!reader.IsDBNull(index))
{
item.CommunityRating = reader.GetFloat(index);
}
index++;
if (query.HasField(ItemFields.CustomRating))
{
if (!reader.IsDBNull(index))
{
item.CustomRating = reader.GetString(index);
}
index++;
}
if (!reader.IsDBNull(index))
{
item.IndexNumber = reader.GetInt32(index);
}
index++;
if (query.HasField(ItemFields.Settings))
{
if (!reader.IsDBNull(index))
{
item.IsLocked = reader.GetBoolean(index);
}
index++;
if (!reader.IsDBNull(index))
{
item.PreferredMetadataLanguage = reader.GetString(index);
}
index++;
if (!reader.IsDBNull(index))
{
item.PreferredMetadataCountryCode = reader.GetString(index);
}
index++;
}
if (!reader.IsDBNull(index))
{
item.IsHD = reader.GetBoolean(index);
}
index++;
if (!reader.IsDBNull(index))
{
item.ExternalEtag = reader.GetString(index);
}
index++;
if (!reader.IsDBNull(index))
{
item.DateLastRefreshed = reader.GetDateTime(index).ToUniversalTime();
}
index++;
if (!reader.IsDBNull(index))
{
item.Name = reader.GetString(index);
}
index++;
if (!reader.IsDBNull(index))
{
item.Path = reader.GetString(index);
}
index++;
if (!reader.IsDBNull(index))
{
item.PremiereDate = reader.GetDateTime(index).ToUniversalTime();
}
index++;
if (query.HasField(ItemFields.Overview))
{
if (!reader.IsDBNull(index))
{
item.Overview = reader.GetString(index);
}
index++;
}
if (!reader.IsDBNull(index))
{
item.ParentIndexNumber = reader.GetInt32(index);
}
index++;
if (!reader.IsDBNull(index))
{
item.ProductionYear = reader.GetInt32(index);
}
index++;
if (!reader.IsDBNull(index))
{
item.OfficialRating = reader.GetString(index);
}
index++;
if (query.HasField(ItemFields.OfficialRatingDescription))
{
if (!reader.IsDBNull(index))
{
item.OfficialRatingDescription = reader.GetString(index);
}
index++;
}
if (query.HasField(ItemFields.HomePageUrl))
{
if (!reader.IsDBNull(index))
{
item.HomePageUrl = reader.GetString(index);
}
index++;
}
if (query.HasField(ItemFields.DisplayMediaType))
{
if (!reader.IsDBNull(index))
{
item.DisplayMediaType = reader.GetString(index);
}
index++;
}
if (query.HasField(ItemFields.SortName))
{
if (!reader.IsDBNull(index))
{
item.ForcedSortName = reader.GetString(index);
}
index++;
}
if (!reader.IsDBNull(index))
{
item.RunTimeTicks = reader.GetInt64(index);
}
index++;
if (query.HasField(ItemFields.VoteCount))
{
if (!reader.IsDBNull(index))
{
item.VoteCount = reader.GetInt32(index);
}
index++;
}
if (query.HasField(ItemFields.DateCreated))
{
if (!reader.IsDBNull(index))
{
item.DateCreated = reader.GetDateTime(index).ToUniversalTime();
}
index++;
}
if (!reader.IsDBNull(index))
{
item.DateModified = reader.GetDateTime(index).ToUniversalTime();
}
index++;
item.Id = reader.GetGuid(index);
index++;
if (query.HasField(ItemFields.Genres))
{
if (!reader.IsDBNull(index))
{
item.Genres = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).ToList();
}
index++;
}
if (!reader.IsDBNull(index))
{
item.ParentId = reader.GetGuid(index);
}
index++;
if (!reader.IsDBNull(index))
{
item.Audio = (ProgramAudio)Enum.Parse(typeof(ProgramAudio), reader.GetString(index), true);
}
index++;
// TODO: Even if not needed by apps, the server needs it internally
// But get this excluded from contexts where it is not needed
if (!reader.IsDBNull(index))
{
item.ServiceName = reader.GetString(index);
}
index++;
if (!reader.IsDBNull(index))
{
item.IsInMixedFolder = reader.GetBoolean(index);
}
index++;
if (!reader.IsDBNull(index))
{
item.DateLastSaved = reader.GetDateTime(index).ToUniversalTime();
}
index++;
if (query.HasField(ItemFields.Settings))
{
if (!reader.IsDBNull(index))
{
item.LockedFields = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).Select(i => (MetadataFields)Enum.Parse(typeof(MetadataFields), i, true)).ToList();
}
index++;
}
if (query.HasField(ItemFields.Studios))
{
if (!reader.IsDBNull(index))
{
item.Studios = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).ToList();
}
index++;
}
if (query.HasField(ItemFields.Tags))
{
if (!reader.IsDBNull(index))
{
item.Tags = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).ToList();
}
index++;
}
if (!reader.IsDBNull(index))
{
item.SourceType = (SourceType)Enum.Parse(typeof(SourceType), reader.GetString(index), true);
}
index++;
var trailer = item as Trailer;
if (trailer != null)
{
if (!reader.IsDBNull(index))
{
trailer.TrailerTypes = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).Select(i => (TrailerType)Enum.Parse(typeof(TrailerType), i, true)).ToList();
}
}
index++;
if (query.HasField(ItemFields.OriginalTitle))
{
if (!reader.IsDBNull(index))
{
item.OriginalTitle = reader.GetString(index);
}
index++;
}
var video = item as Video;
if (video != null)
{
if (!reader.IsDBNull(index))
{
video.PrimaryVersionId = reader.GetString(index);
}
}
index++;
if (query.HasField(ItemFields.DateLastMediaAdded))
{
var folder = item as Folder;
if (folder != null && !reader.IsDBNull(index))
{
folder.DateLastMediaAdded = reader.GetDateTime(index).ToUniversalTime();
}
index++;
}
if (!reader.IsDBNull(index))
{
item.Album = reader.GetString(index);
}
index++;
if (!reader.IsDBNull(index))
{
item.CriticRating = reader.GetFloat(index);
}
index++;
if (query.HasField(ItemFields.CriticRatingSummary))
{
if (!reader.IsDBNull(index))
{
item.CriticRatingSummary = reader.GetString(index);
}
index++;
}
if (!reader.IsDBNull(index))
{
item.IsVirtualItem = reader.GetBoolean(index);
}
index++;
var hasSeries = item as IHasSeries;
if (hasSeries != null)
{
if (!reader.IsDBNull(index))
{
hasSeries.SeriesName = reader.GetString(index);
}
}
index++;
var episode = item as Episode;
if (episode != null)
{
if (!reader.IsDBNull(index))
{
episode.SeasonName = reader.GetString(index);
}
index++;
if (!reader.IsDBNull(index))
{
episode.SeasonId = reader.GetGuid(index);
}
}
else
{
index++;
}
index++;
if (hasSeries != null)
{
if (!reader.IsDBNull(index))
{
hasSeries.SeriesId = reader.GetGuid(index);
}
}
index++;
if (hasSeries != null)
{
if (!reader.IsDBNull(index))
{
hasSeries.SeriesSortName = reader.GetString(index);
}
}
index++;
if (!reader.IsDBNull(index))
{
item.PresentationUniqueKey = reader.GetString(index);
}
index++;
if (!reader.IsDBNull(index))
{
item.InheritedParentalRatingValue = reader.GetInt32(index);
}
index++;
if (!reader.IsDBNull(index))
{
item.InheritedTags = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).ToList();
}
index++;
if (!reader.IsDBNull(index))
{
item.ExternalSeriesId = reader.GetString(index);
}
index++;
if (query.HasField(ItemFields.ShortOverview))
{
if (!reader.IsDBNull(index))
{
item.ShortOverview = reader.GetString(index);
}
index++;
}
if (query.HasField(ItemFields.Taglines))
{
if (!reader.IsDBNull(index))
{
item.Tagline = reader.GetString(index);
}
index++;
}
if (query.HasField(ItemFields.Keywords))
{
if (!reader.IsDBNull(index))
{
item.Keywords = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).ToList();
}
index++;
}
if (!reader.IsDBNull(index))
{
DeserializeProviderIds(reader.GetString(index), item);
}
index++;
if (query.DtoOptions.EnableImages)
{
if (!reader.IsDBNull(index))
{
DeserializeImages(reader.GetString(index), item);
}
index++;
}
if (query.HasField(ItemFields.ProductionLocations))
{
if (!reader.IsDBNull(index))
{
item.ProductionLocations = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).ToList();
}
index++;
}
if (query.HasField(ItemFields.ThemeSongIds))
{
if (!reader.IsDBNull(index))
{
item.ThemeSongIds = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).Select(i => new Guid(i)).ToList();
}
index++;
}
if (query.HasField(ItemFields.ThemeVideoIds))
{
if (!reader.IsDBNull(index))
{
item.ThemeVideoIds = reader.GetString(index).Split('|').Where(i => !string.IsNullOrWhiteSpace(i)).Select(i => new Guid(i)).ToList();
}
index++;
}
if (string.IsNullOrWhiteSpace(item.Tagline))
{
var movie = item as Movie;
if (movie != null && movie.Taglines.Count > 0)
{
movie.Tagline = movie.Taglines[0];
}
}
if (type == typeof(Person) && item.ProductionLocations.Count == 0)
{
var person = (Person)item;
if (!string.IsNullOrWhiteSpace(person.PlaceOfBirth))
{
item.ProductionLocations = new List<string> { person.PlaceOfBirth };
}
}
return item;
}
