private SharedArtistStatsContract GetSharedArtistStats(IDatabaseContext <Artist> ctx, Artist artist)
{
var key = string.Format("ArtistQueries.SharedArtistStatsContract.{0}", artist.Id);
return(cache.GetOrInsert(key, CachePolicy.AbsoluteExpiration(1), () => {
try {
var stats = ctx.Query()
.Where(a => a.Id == artist.Id)
.Select(a => new {
FollowCount = a.Users.Count,
AlbumCount = a.AllAlbums.Count(l => !l.Album.Deleted),
RatedAlbumCount = a.AllAlbums.Count(l => !l.Album.Deleted && l.Album.RatingCount > 0),
SongCount = a.AllSongs.Count(s => !s.Song.Deleted),
RatedSongCount = a.AllSongs.Count(s => !s.Song.Deleted && s.Song.RatingScore > 0),
AlbumRatingsTotalCount = a.AllAlbums.Any() ? a.AllAlbums.Sum(l => l.Album.RatingCount) : 0,
AlbumRatingsTotalSum = a.AllAlbums.Any() ? a.AllAlbums.Sum(l => l.Album.RatingTotal) : 0
})
.FirstOrDefault();
return new SharedArtistStatsContract {
AlbumCount = stats.AlbumCount,
FollowerCount = stats.FollowCount,
RatedAlbumCount = stats.RatedAlbumCount,
SongCount = stats.SongCount,
RatedSongCount = stats.RatedSongCount,
AlbumRatingAverage = (stats.AlbumRatingsTotalCount > 0 ? Math.Round(stats.AlbumRatingsTotalSum / (double)stats.AlbumRatingsTotalCount, 2) : 0)
};
} catch (HibernateException x) {
// TODO: Loading of stats timeouts sometimes. Since they're not essential we can accept returning only partial stats.
// However, this should be fixed by tuning the queries further.
log.Error(x, "Unable to load shared artist stats");
return new SharedArtistStatsContract();
}
}));
}
private CachedUserStats GetCachedUserStats(IRepositoryContext <User> ctx, User user)
{
var key = string.Format("CachedUserStats.{0}", user.Id);
return(cache.GetOrInsert(key, CachePolicy.AbsoluteExpiration(1), () => {
var stats = new CachedUserStats();
stats.CommentCount
= ctx.Query <AlbumComment>().Count(c => c.Author.Id == user.Id)
+ ctx.Query <ArtistComment>().Count(c => c.Author.Id == user.Id)
+ ctx.Query <SongComment>().Count(c => c.Author.Id == user.Id);
stats.EditCount
= ctx.Query <ArchivedAlbumVersion>().Count(c => c.Author.Id == user.Id)
+ ctx.Query <ArchivedArtistVersion>().Count(c => c.Author.Id == user.Id)
+ ctx.Query <ArchivedSongVersion>().Count(c => c.Author.Id == user.Id);
stats.SubmitCount
= ctx.Query <ArchivedAlbumVersion>().Count(c => c.Author.Id == user.Id && c.Version == 0)
+ ctx.Query <ArchivedArtistVersion>().Count(c => c.Author.Id == user.Id && c.Version == 0)
+ ctx.Query <ArchivedSongVersion>().Count(c => c.Author.Id == user.Id && c.Version == 0);
stats.TagVotes
= ctx.Query <SongTagVote>().Count(t => t.User.Id == user.Id)
+ ctx.Query <AlbumTagVote>().Count(t => t.User.Id == user.Id)
+ ctx.Query <ArtistTagVote>().Count(t => t.User.Id == user.Id);
return stats;
}));
}
private SharedArtistStatsContract GetSharedArtistStats(IRepositoryContext <Artist> ctx, Artist artist)
{
var key = string.Format("SharedArtistStatsContract.{0}", artist.Id);
return(cache.GetOrInsert(key, CachePolicy.AbsoluteExpiration(1), () => {
var stats = ctx.Query()
.Where(a => a.Id == artist.Id)
.Select(a => new {
AlbumCount = a.AllAlbums.Count(l => !l.Album.Deleted),
RatedAlbumCount = a.AllAlbums.Count(l => !l.Album.Deleted && l.Album.RatingCount > 0),
SongCount = a.AllSongs.Count(s => !s.Song.Deleted),
RatedSongCount = a.AllSongs.Count(s => !s.Song.Deleted && s.Song.RatingScore > 0),
AlbumRatingsTotalCount = a.AllAlbums.Any() ? a.AllAlbums.Sum(l => l.Album.RatingCount) : 0,
AlbumRatingsTotalSum = a.AllAlbums.Any() ? a.AllAlbums.Sum(l => l.Album.RatingTotal) : 0
})
.FirstOrDefault();
return new SharedArtistStatsContract {
AlbumCount = stats.AlbumCount,
RatedAlbumCount = stats.RatedAlbumCount,
SongCount = stats.SongCount,
RatedSongCount = stats.RatedSongCount,
AlbumRatingAverage = (stats.AlbumRatingsTotalCount > 0 ? Math.Round(stats.AlbumRatingsTotalSum / (double)stats.AlbumRatingsTotalCount, 2) : 0)
};
}));
}
private AdvancedArtistStatsContract GetAdvancedStats(IDatabaseContext <Artist> ctx, Artist artist)
{
if (artist.ArtistType != ArtistType.Producer && artist.ArtistType != ArtistType.CoverArtist)
{
return(null);
}
var key = $"ArtistQueries.AdvancedArtistStatsContract.{artist.Id}";
var cached = _cache.GetOrInsert(key, CachePolicy.AbsoluteExpiration(24), () =>
{
var topVocaloids = new ArtistRelationsQuery(ctx, LanguagePreference, _cache, _imageUrlFactory).GetTopVoicebanks(artist);
return(new CachedAdvancedArtistStatsContract
{
TopVocaloids = topVocaloids
});
});
return(new AdvancedArtistStatsContract
{
TopVocaloids = cached.TopVocaloids.Select(v => new TopStatContract <ArtistContract>
{
Data = new ArtistContract(v.Data, LanguagePreference),
Count = v.Count
}).ToArray()
});
}
private AlbumForApiContract[] GetRecentAlbums(ISession session, ContentLanguagePreference languagePreference, AlbumOptionalFields fields)
{
var cacheKey = $"OtherService.RecentAlbums.{languagePreference}";
return(_cache.GetOrInsert(cacheKey, CachePolicy.AbsoluteExpiration(TimeSpan.FromHours(1)), () =>
{
var now = DateTime.Now;
var upcoming = session.Query <Album>()
.WhereHasArtist(AppConfig.FilteredArtistId)
.Where(a => !a.Deleted)
.WhereHasReleaseDate()
.WhereReleaseDateIsAfter(now)
.OrderByReleaseDate(SortDirection.Ascending)
.Take(4)
.ToArray();
var recent = session.Query <Album>()
.WhereHasArtist(AppConfig.FilteredArtistId)
.Where(a => !a.Deleted)
.WhereHasReleaseDate()
.WhereReleaseDateIsBefore(now)
.OrderByReleaseDate(SortDirection.Descending)
.Take(3)
.ToArray();
var newAlbumContracts = upcoming.Reverse().Concat(recent)
.Select(a => new AlbumForApiContract(a, null, languagePreference, _thumbPersister, fields, SongOptionalFields.None))
.ToArray();
return newAlbumContracts;
}));
}
public Dictionary <string, string> GetMappings()
{
return(cache.GetOrInsert(cacheKey, CachePolicy.AbsoluteExpiration(24), () => {
var mappings = EmbeddedResourceManager.NicoTagMapping;
return mappings.ToDictionary(m => m.Key, m => m.Value, StringComparer.InvariantCultureIgnoreCase);
}));
}
private async Task <TagStatsContract> GetStatsAsync(IDatabaseContext <Tag> ctx, int tagId)
{
var key = $"TagQueries.GetStats.{tagId}.{LanguagePreference}";
return(await _cache.GetOrInsertAsync(key, CachePolicy.AbsoluteExpiration(1), async() =>
{
var artists = await GetTopUsagesAndCountAsync <ArtistTagUsage, Artist, int>(ctx, tagId, t => !t.Entry.Deleted, t => t.Entry.Id, t => t.Entry);
var albums = await GetTopUsagesAndCountAsync <AlbumTagUsage, Album, int>(ctx, tagId, t => !t.Entry.Deleted, t => t.Entry.RatingTotal, t => t.Entry);
var songLists = await GetTopUsagesAndCountAsync <SongListTagUsage, SongList, int>(ctx, tagId, t => !t.Entry.Deleted, t => t.Entry.Id, t => t.Entry);
var songs = await GetTopUsagesAndCountAsync <SongTagUsage, Song, int, SongType>(ctx, tagId, EntryType.Song, (query, etm) => query.WhereHasTypeOrTag(etm));
var eventSeries = await GetTopUsagesAndCountAsync <EventSeriesTagUsage, ReleaseEventSeries, int>(ctx, tagId, t => !t.Entry.Deleted, t => t.Entry.Id, t => t.Entry, maxCount: 6);
var seriesIds = eventSeries.TopUsages.Select(e => e.Id).ToArray();
var eventDateCutoff = DateTime.Now.AddDays(-7);
var events = await GetTopUsagesAndCountAsync <EventTagUsage, ReleaseEvent, int>(ctx, tagId, t => !t.Entry.Deleted &&
(t.Entry.Series == null || (t.Entry.Date.DateTime != null && t.Entry.Date.DateTime >= eventDateCutoff) || !seriesIds.Contains(t.Entry.Series.Id)), t => t.Entry.Id, t => t.Entry, maxCount: 6);
var followerCount = await ctx.Query <TagForUser>().Where(t => t.Tag.Id == tagId).VdbCountAsync();
var stats = new TagStatsContract(LanguagePreference, _thumbStore,
artists.TopUsages, artists.TotalCount,
albums.TopUsages, albums.TotalCount,
songLists.TopUsages, songLists.TotalCount,
songs.TopUsages, songs.TotalCount,
eventSeries.TopUsages, eventSeries.TotalCount,
events.TopUsages, events.TotalCount,
followerCount);
return stats;
}));
}
private int[] GetLatestSongIds(IDatabaseContext ctx, Artist artist)
{
var cacheKey = string.Format("ArtistRelationsQuery.GetLatestSongs.{0}", artist.Id);
return(cache.GetOrInsert(cacheKey, CachePolicy.AbsoluteExpiration(TimeSpan.FromMinutes(5)), () => {
return ctx.Query <ArtistForSong>()
.Where(s => !s.Song.Deleted && s.Artist.Id == artist.Id && !s.IsSupport)
.WhereIsMainSong(artist.ArtistType)
.OrderByPublishDate(SortDirection.Descending)
.Select(s => s.Song.Id)
.Take(8)
.ToArray();
}, allowCaching: ids => ids.Length >= 8));
}
private EntryWithCommentsContract[] GetRecentComments(ISession session, bool ssl)
{
var cacheKey = string.Format("OtherService.RecentComments.{0}.{1}", LanguagePreference, ssl);
var item = (EntryWithCommentsContract[])cache.Get(cacheKey);
if (item != null)
{
return(item);
}
item = GetRecentComments(session, 9, ssl);
cache.Add(cacheKey, item, CachePolicy.AbsoluteExpiration(TimeSpan.FromMinutes(5)));
return(item);
}
private SongListBaseContract[] GetSongPools(IDatabaseContext <Song> ctx, int songId)
{
var cacheKey = string.Format("GetSongPools.{0}", songId);
return(cache.GetOrInsert(cacheKey, CachePolicy.AbsoluteExpiration(1), () => {
var lists = ctx
.Query <SongList>()
.Where(s => s.FeaturedCategory == SongListFeaturedCategory.Pools && s.AllSongs.Any(l => l.Song.Id == songId))
.OrderBy(s => s.Name)
.Take(3)
.ToArray();
return lists.Select(s => new SongListBaseContract(s)).ToArray();
}));
}
private async Task <EntryWithCommentsContract[]> GetRecentCommentsAsync(ISession session)
{
var cacheKey = string.Format("OtherService.RecentComments.{0}", LanguagePreference);
var item = (EntryWithCommentsContract[])cache.Get(cacheKey);
if (item != null)
{
return(item);
}
item = await GetRecentCommentsAsync(session, 9);
cache.Add(cacheKey, item, CachePolicy.AbsoluteExpiration(TimeSpan.FromMinutes(5)));
return(item);
}
private AdvancedArtistStatsContract GetAdvancedStats(IDatabaseContext <Artist> ctx, Artist artist)
{
if (artist.ArtistType != ArtistType.Producer)
{
return(null);
}
var key = string.Format("ArtistQueries.AdvancedArtistStatsContract.{0}", artist.Id);
var cached = cache.GetOrInsert(key, CachePolicy.AbsoluteExpiration(24), () => {
var types = new[] { ArtistType.Vocaloid, ArtistType.UTAU, ArtistType.CeVIO, ArtistType.OtherVoiceSynthesizer };
var topVocaloidIdsAndCounts = ctx
.Query <ArtistForSong>()
.Where(a => types.Contains(a.Artist.ArtistType) && !a.Song.Deleted && a.Song.AllArtists.Any(ar => !ar.IsSupport && ar.Artist.Id == artist.Id))
.GroupBy(a => a.Artist.Id)
.Select(a => new {
ArtistId = a.Key,
Count = a.Count()
})
.OrderByDescending(a => a.Count)
.Take(3)
.ToDictionary(a => a.ArtistId, a => a.Count);
var topVocaloidIds = topVocaloidIdsAndCounts.Select(i => i.Key).ToArray();
var topVocaloids = ctx.Query().Where(a => topVocaloidIds.Contains(a.Id))
.ToArray()
.Select(a => new TopStatContract <TranslatedArtistContract> {
Data = new TranslatedArtistContract(a),
Count = topVocaloidIdsAndCounts[a.Id]
})
.OrderByDescending(d => d.Count)
.ToArray();
return(new CachedAdvancedArtistStatsContract {
TopVocaloids = topVocaloids
});
});
return(new AdvancedArtistStatsContract {
TopVocaloids = cached.TopVocaloids.Select(v => new TopStatContract <ArtistContract> {
Data = new ArtistContract(v.Data, LanguagePreference),
Count = v.Count
}).ToArray()
});
}
/// <summary>
/// Stats shared for all users. These are cached for 1 hour.
/// </summary>
private SharedAlbumStatsContract GetSharedAlbumStats(IDatabaseContext ctx, Album album)
{
var key = string.Format("AlbumQueries.SharedAlbumStatsContract.{0}", album.Id);
return(cache.GetOrInsert(key, CachePolicy.AbsoluteExpiration(1), () => {
var latestReview = album.LastReview;
var latestRatingScore = latestReview != null ? album.UserCollections.FirstOrDefault(uc => uc.User.Equals(latestReview.User)) : null;
return new SharedAlbumStatsContract {
ReviewCount = album.Reviews.Count,
LatestReview = latestReview != null ? new AlbumReviewContract(latestReview, userIconFactory) : null,
LatestReviewRatingScore = latestRatingScore?.Rating ?? 0,
OwnedCount = album.UserCollections.Count(au => au.PurchaseStatus == PurchaseStatus.Owned),
WishlistCount = album.UserCollections.Count(au => au.PurchaseStatus == PurchaseStatus.Wishlisted),
};
}));
}
/// <summary>
/// Stats related to logged in user.
/// These stats are cached for 1 hour.
/// </summary>
private PersonalArtistStatsContract GetPersonalArtistStats(IDatabaseContext <Artist> ctx, Artist artist)
{
if (!PermissionContext.IsLoggedIn)
{
return(null);
}
var key = string.Format("ArtistQueries.PersonalArtistStatsContract.{0}.{1}", artist.Id, PermissionContext.LoggedUserId);
return(cache.GetOrInsert(key, CachePolicy.AbsoluteExpiration(1), () => {
return new PersonalArtistStatsContract {
SongRatingCount = ctx.OfType <FavoriteSongForUser>()
.Query()
.Count(f => f.User.Id == PermissionContext.LoggedUserId && f.Song.AllArtists.Any(a => a.Artist.Id == artist.Id))
};
}));
}
private Song[] GetSongSuggestions(IDatabaseContext <Song> ctx, Song song)
{
var cacheKey = string.Format("GetSongSuggestions.{0}", song.Id);
var songIds = cache.GetOrInsert(cacheKey, CachePolicy.AbsoluteExpiration(24), () => {
var related = new RelatedSongsQuery(ctx).GetLikeMatches(song, new[] { song.Id }, 4);
return(related);
});
if (!songIds.Any())
{
return(new Song[0]);
}
return(ctx.Query()
.Where(s => songIds.Contains(s.Id))
.ToArray());
}
private ReleaseEventForApiContract[] GetLatestEvents(IDatabaseContext session, Artist artist)
{
var id = artist.Id;
var cacheKey = $"{nameof(ArtistRelationsQuery)}.{nameof(GetLatestEvents)}.{id}.{languagePreference}";
return(cache.GetOrInsert(cacheKey, CachePolicy.AbsoluteExpiration(TimeSpan.FromHours(4)), () => {
return session.Query <ReleaseEvent>()
.WhereNotDeleted()
.Where(e => e.AllArtists.Any(a => a.Artist.Id == id))
.WhereDateIsBetween(begin: null, end: DateTime.Today.AddMonths(6))
.OrderByDate(SortDirection.Descending)
.Take(3).ToArray()
.Select(s => new ReleaseEventForApiContract(s, languagePreference,
ReleaseEventOptionalFields.AdditionalNames | ReleaseEventOptionalFields.MainPicture | ReleaseEventOptionalFields.Series | ReleaseEventOptionalFields.Venue,
entryThumbPersister))
.ToArray();
}));
}
private EntryForApiContract[] GetRecentEvents(ISession session, bool ssl)
{
var count = 3;
var cacheKey = string.Format("OtherService.RecentEvents.{0}.{1}", LanguagePreference, ssl);
return(cache.GetOrInsert(cacheKey, CachePolicy.AbsoluteExpiration(24), () => {
var minDate = DateTime.Now - TimeSpan.FromDays(2);
var maxDate = DateTime.Now + TimeSpan.FromDays(14);
var recentEvents = session.Query <ReleaseEvent>()
.WhereDateIsBetween(minDate, maxDate)
.OrderByDate(SortDirection.Descending)
.Take(count)
.ToArray();
var entryContracts = recentEvents.Select(i => entryForApiContractFactory.Create(i, EntryOptionalFields.MainPicture, LanguagePreference, ssl));
return entryContracts.ToArray();
}));
}
private int[] GetTopSongIds(IDatabaseContext ctx, Artist artist, SongForApiContract[] latestSongs)
{
var cacheKey = string.Format("ArtistRelationsQuery.GetTopSongs.{0}", artist.Id);
return(cache.GetOrInsert(cacheKey, CachePolicy.AbsoluteExpiration(1), () => {
var latestSongIds = latestSongs != null ? latestSongs.Select(s => s.Id).ToArray() : new int[0];
return ctx.Query <ArtistForSong>()
.Where(s => !s.Song.Deleted &&
s.Artist.Id == artist.Id &&
!s.IsSupport &&
s.Roles != ArtistRoles.VocalDataProvider &&
s.Song.RatingScore > 0 &&
!latestSongIds.Contains(s.Song.Id))
.OrderBy(SongSortRule.RatingScore, languagePreference)
.Select(s => s.Song.Id)
.Take(8)
.ToArray();
}));
}
private ReleaseEventForApiContract[] GetRecentEvents(ISession session)
{
var count = 3;
var cacheKey = string.Format("OtherService.RecentEvents.{0}", LanguagePreference);
return(cache.GetOrInsert(cacheKey, CachePolicy.AbsoluteExpiration(24), () => {
var minDate = DateTime.Now - TimeSpan.FromDays(2);
var maxDate = DateTime.Now + TimeSpan.FromDays(14);
var recentEvents = session.Query <ReleaseEvent>()
.WhereNotDeleted()
.WhereDateIsBetween(minDate, maxDate)
.OrderByDate(SortDirection.Ascending)
.Take(count)
.ToArray();
var entryContracts = recentEvents.Select(i =>
new ReleaseEventForApiContract(i, LanguagePreference, ReleaseEventOptionalFields.AdditionalNames | ReleaseEventOptionalFields.MainPicture | ReleaseEventOptionalFields.Series | ReleaseEventOptionalFields.Venue,
thumbPersister));
return entryContracts.ToArray();
}));
}
private EntryForApiContract[] GetRecentEvents(ISession session, bool ssl)
{
var count = 3;
var cacheKey = string.Format("OtherService.RecentEvents.{0}.{1}", LanguagePreference, ssl);
return(cache.GetOrInsert(cacheKey, CachePolicy.AbsoluteExpiration(24), () => {
var minDate = DateTime.Now - TimeSpan.FromDays(2);
var maxDate = DateTime.Now + TimeSpan.FromDays(14);
var recentEvents = session.Query <ReleaseEvent>()
.WhereDateIsBetween(minDate, maxDate)
.OrderByDate(SortDirection.Descending)
.Take(count)
.ToArray();
var recentConcerts = session.Query <SongList>()
.Where(s => s.FeaturedCategory == SongListFeaturedCategory.Concerts)
.WhereEventDateIsBetween(minDate, maxDate)
.OrderByDate(SortDirection.Descending)
.Take(count)
.ToArray();
var items = recentEvents.Select(e => new {
Entry = (IEntryWithNames)e,
e.Date
}).Concat(recentConcerts.Select(e => new {
Entry = (IEntryWithNames)e,
Date = e.EventDate
}))
.OrderByDescending(e => e.Date.DateTime)
.Take(count);
var entryContracts = items.Select(i => entryForApiContractFactory.Create(i.Entry, EntryOptionalFields.MainPicture, LanguagePreference, ssl));
return entryContracts.ToArray();
}));
}
private AlbumForApiContract[] GetTopAlbumsCached(ISession session, int[] recentIds, ContentLanguagePreference languagePreference, AlbumOptionalFields fields)
{
var cacheKey = $"OtherService.PopularAlbums.{languagePreference}";
return(_cache.GetOrInsert(cacheKey, CachePolicy.AbsoluteExpiration(TimeSpan.FromHours(24)), () => GetTopAlbums(session, recentIds, languagePreference, fields)));
}
