public async Task Fix(CommandContext ctx, [Description("Product ID to reset")] string productId) { var linksToRemove = new List <(string contentId, string link)>(); using (var db = new ThumbnailDb()) { var items = db.Thumbnail.Where(i => i.ProductCode == productId && !string.IsNullOrEmpty(i.EmbeddableUrl)); foreach (var thumb in items) { linksToRemove.Add((thumb.ContentId, thumb.EmbeddableUrl)); thumb.EmbeddableUrl = null; } await db.SaveChangesAsync(Config.Cts.Token).ConfigureAwait(false); } await TryDeleteThumbnailCache(ctx, linksToRemove).ConfigureAwait(false); await ctx.RespondAsync($"Removed {linksToRemove.Count} cached links").ConfigureAwait(false); }
public async Task Rename(CommandContext ctx, [Description("Product code such as BLUS12345")] string productCode, [RemainingText, Description("New game title to save in the database")] string title) { productCode = productCode.ToUpperInvariant(); using var db = new ThumbnailDb(); var item = db.Thumbnail.FirstOrDefault(t => t.ProductCode == productCode); if (item == null) { await ctx.ReactWithAsync(Config.Reactions.Failure, $"Unknown product code {productCode}", true).ConfigureAwait(false); } else { item.Name = title; await db.SaveChangesAsync().ConfigureAwait(false); await ctx.ReactWithAsync(Config.Reactions.Success, "Title updated successfully").ConfigureAwait(false); } }
public async Task Add(CommandContext ctx, [Description("Product code such as BLUS12345")] string contentId, [RemainingText, Description("New game title to save in the database")] string title) { contentId = contentId.ToUpperInvariant(); var productCode = contentId; var productCodeMatch = ProductCodeLookup.ProductCode.Match(contentId); var contentIdMatch = PsnScraper.ContentIdMatcher.Match(contentId); if (contentIdMatch.Success) { productCode = contentIdMatch.Groups["product_id"].Value; } else if (productCodeMatch.Success) { productCode = productCodeMatch.Groups["letters"].Value + productCodeMatch.Groups["numbers"].Value; contentId = null; } else { await ctx.ReactWithAsync(Config.Reactions.Failure, "Invalid content id", true).ConfigureAwait(false); return; } using var db = new ThumbnailDb(); var item = db.Thumbnail.FirstOrDefault(t => t.ProductCode == productCode); if (item != null) { await ctx.ReactWithAsync(Config.Reactions.Failure, $"Product code {contentId} already exists", true).ConfigureAwait(false); } else { item = new Thumbnail { ProductCode = contentId, ContentId = contentId, Name = title, }; db.Add(item); await db.SaveChangesAsync().ConfigureAwait(false); await ctx.ReactWithAsync(Config.Reactions.Success, "Title added successfully").ConfigureAwait(false); } }
public static async Task SetLastRunTimestampAsync(string locale, string?containerId = null) { if (string.IsNullOrEmpty(locale)) { throw new ArgumentException("Locale is mandatory", nameof(locale)); } var id = GetId(locale, containerId); await using var db = new ThumbnailDb(); var timestamp = db.State.FirstOrDefault(s => s.Locale == id); if (timestamp == null) { await db.State.AddAsync(new State { Locale = id, Timestamp = DateTime.UtcNow.Ticks }).ConfigureAwait(false); } else { timestamp.Timestamp = DateTime.UtcNow.Ticks; } await db.SaveChangesAsync().ConfigureAwait(false); }
public async Task Rename(CommandContext ctx, [Description("Old function name")] string oldFunctionName, [Description("New function name")] string newFunctionName) { using var db = new ThumbnailDb(); var oldMatches = await db.SyscallInfo.Where(sci => sci.Function == oldFunctionName).ToListAsync().ConfigureAwait(false); if (oldMatches.Count == 0) { await ctx.RespondAsync($"Function `{oldFunctionName}` could not be found").ConfigureAwait(false); await Search(ctx, oldFunctionName).ConfigureAwait(false); return; } if (oldMatches.Count > 1) { await ctx.RespondAsync("More than one matching function was found, I can't handle this right now 😔").ConfigureAwait(false); await Search(ctx, oldFunctionName).ConfigureAwait(false); return; } var conflicts = await db.SyscallInfo.Where(sce => sce.Function == newFunctionName).AnyAsync().ConfigureAwait(false); if (conflicts) { await ctx.RespondAsync($"There is already a function `{newFunctionName}`").ConfigureAwait(false); await Search(ctx, newFunctionName).ConfigureAwait(false); return; } oldMatches[0].Function = newFunctionName; await db.SaveChangesAsync().ConfigureAwait(false); await ctx.RespondAsync($"Function `{oldFunctionName}` was successfully renamed to `{newFunctionName}`").ConfigureAwait(false); }
public static async Task <(int funcs, int links)> FixInvalidFunctionNamesAsync() { var syscallStats = new TSyscallStats(); int funcs = 0, links = 0; using (var db = new ThumbnailDb()) { var funcsToFix = new List <SyscallInfo>(0); try { funcsToFix = await db.SyscallInfo.Where(sci => sci.Function.Contains('(')).ToListAsync().ConfigureAwait(false); funcs = funcsToFix.Count; if (funcs == 0) { return(0, 0); } foreach (var sci in funcsToFix) { var productIds = await db.SyscallToProductMap.AsNoTracking().Where(m => m.SyscallInfoId == sci.Id).Select(m => m.Product.ProductCode).Distinct().ToListAsync().ConfigureAwait(false); links += productIds.Count; foreach (var productId in productIds) { if (!syscallStats.TryGetValue(productId, out var scInfo)) { syscallStats[productId] = scInfo = new Dictionary <string, HashSet <string> >(); } if (!scInfo.TryGetValue(sci.Module, out var smInfo)) { scInfo[sci.Module] = smInfo = new HashSet <string>(); } smInfo.Add(sci.Function.Split('(', 2)[0]); } } } catch (Exception e) { Config.Log.Warn(e, "Failed to build fixed syscall mappings"); throw e; } await SaveAsync(syscallStats).ConfigureAwait(false); if (await Limiter.WaitAsync(1000, Config.Cts.Token)) { try { db.SyscallInfo.RemoveRange(funcsToFix); await db.SaveChangesAsync().ConfigureAwait(false); } catch (Exception e) { Config.Log.Warn(e, "Failed to remove broken syscall mappings"); throw e; } finally { Limiter.Release(); } } } return(funcs, links); }
public static async Task <(int funcs, int links)> FixDuplicatesAsync() { int funcs = 0, links = 0; using (var db = new ThumbnailDb()) { var duplicateFunctionNames = await db.SyscallInfo.Where(sci => db.SyscallInfo.Count(isci => isci.Function == sci.Function && isci.Module == sci.Module) > 1).Distinct().ToListAsync().ConfigureAwait(false); if (duplicateFunctionNames.Count == 0) { return(0, 0); } if (await Limiter.WaitAsync(1000, Config.Cts.Token)) { try { foreach (var dupFunc in duplicateFunctionNames) { var dups = db.SyscallInfo.Where(sci => sci.Function == dupFunc.Function && sci.Module == dupFunc.Module).ToList(); if (dups.Count < 2) { continue; } var mostCommonDup = dups.Select(dup => (dup, count: db.SyscallToProductMap.Count(scm => scm.SyscallInfoId == dup.Id))).OrderByDescending(stat => stat.count).First().dup; var dupsToRemove = dups.Where(df => df.Id != mostCommonDup.Id).ToList(); funcs += dupsToRemove.Count; foreach (var dupToRemove in dupsToRemove) { var mappings = db.SyscallToProductMap.Where(scm => scm.SyscallInfoId == dupToRemove.Id).ToList(); links += mappings.Count; foreach (var mapping in mappings) { if (!db.SyscallToProductMap.Any(scm => scm.ProductId == mapping.ProductId && scm.SyscallInfoId == mostCommonDup.Id)) { db.SyscallToProductMap.Add(new SyscallToProductMap { ProductId = mapping.ProductId, SyscallInfoId = mostCommonDup.Id }); } } } await db.SaveChangesAsync().ConfigureAwait(false); db.SyscallInfo.RemoveRange(dupsToRemove); await db.SaveChangesAsync().ConfigureAwait(false); } await db.SaveChangesAsync().ConfigureAwait(false); } catch (Exception e) { Config.Log.Warn(e, "Failed to remove duplicate syscall entries"); throw; } finally { Limiter.Release(); } } } return(funcs, links); }
public static async Task <string> GetThumbnailUrlAsync(this DiscordClient client, string productCode) { productCode = productCode.ToUpperInvariant(); using (var db = new ThumbnailDb()) { var thumb = await db.Thumbnail.FirstOrDefaultAsync(t => t.ProductCode == productCode.ToUpperInvariant()).ConfigureAwait(false); //todo: add search task if not found if (thumb?.EmbeddableUrl is string embeddableUrl && !string.IsNullOrEmpty(embeddableUrl)) { return(embeddableUrl); } if (string.IsNullOrEmpty(thumb?.Url) || !ScrapeStateProvider.IsFresh(thumb.Timestamp)) { var gameTdbCoverUrl = await GameTdbScraper.GetThumbAsync(productCode).ConfigureAwait(false); if (!string.IsNullOrEmpty(gameTdbCoverUrl)) { if (thumb == null) { var addResult = await db.Thumbnail.AddAsync(new Thumbnail { ProductCode = productCode, Url = gameTdbCoverUrl }).ConfigureAwait(false); thumb = addResult.Entity; } else { thumb.Url = gameTdbCoverUrl; } thumb.Timestamp = DateTime.UtcNow.Ticks; await db.SaveChangesAsync().ConfigureAwait(false); } } if (thumb?.Url is string url && !string.IsNullOrEmpty(url)) { if (!string.IsNullOrEmpty(Path.GetExtension(url))) { thumb.EmbeddableUrl = url; await db.SaveChangesAsync().ConfigureAwait(false); return(url); } try { using (var imgStream = await HttpClient.GetStreamAsync(url).ConfigureAwait(false)) using (var memStream = new MemoryStream()) { await imgStream.CopyToAsync(memStream).ConfigureAwait(false); // minimum jpg size is 119 bytes, png is 67 bytes if (memStream.Length < 64) { return(null); } memStream.Seek(0, SeekOrigin.Begin); var spam = await client.GetChannelAsync(Config.ThumbnailSpamId).ConfigureAwait(false); //var message = await spam.SendFileAsync(memStream, (thumb.ContentId ?? thumb.ProductCode) + ".jpg").ConfigureAwait(false); var contentName = (thumb.ContentId ?? thumb.ProductCode); var message = await spam.SendFileAsync(contentName + ".jpg", memStream, contentName).ConfigureAwait(false); thumb.EmbeddableUrl = message.Attachments.First().Url; await db.SaveChangesAsync().ConfigureAwait(false); return(thumb.EmbeddableUrl); } } catch (Exception e) { Config.Log.Warn(e); } } } return(null); }
public static async Task <(string url, DiscordColor color)> GetThumbnailUrlWithColorAsync(DiscordClient client, string contentId, DiscordColor defaultColor, string url = null) { if (string.IsNullOrEmpty(contentId)) { throw new ArgumentException("ContentID can't be empty", nameof(contentId)); } contentId = contentId.ToUpperInvariant(); using var db = new ThumbnailDb(); var info = await db.TitleInfo.FirstOrDefaultAsync(ti => ti.ContentId == contentId, Config.Cts.Token).ConfigureAwait(false); if (info == null) { info = new TitleInfo { ContentId = contentId, ThumbnailUrl = url, Timestamp = DateTime.UtcNow.Ticks }; var thumb = await db.Thumbnail.FirstOrDefaultAsync(t => t.ContentId == contentId).ConfigureAwait(false); if (thumb?.EmbeddableUrl is string eUrl && thumb.Url is string thumbUrl && thumbUrl == url) { info.ThumbnailEmbeddableUrl = eUrl; } info = db.TitleInfo.Add(info).Entity; await db.SaveChangesAsync(Config.Cts.Token).ConfigureAwait(false); } DiscordColor?analyzedColor = null; if (string.IsNullOrEmpty(info.ThumbnailEmbeddableUrl)) { var em = await GetEmbeddableUrlAsync(client, contentId, info.ThumbnailUrl).ConfigureAwait(false); if (em.url is string eUrl) { info.ThumbnailEmbeddableUrl = eUrl; if (em.image is byte[] jpg) { analyzedColor = ColorGetter.Analyze(jpg, defaultColor); var c = analyzedColor.Value.Value; if (c != defaultColor.Value) { info.EmbedColor = c; } } await db.SaveChangesAsync(Config.Cts.Token).ConfigureAwait(false); } } if ((!info.EmbedColor.HasValue && !analyzedColor.HasValue) || (info.EmbedColor.HasValue && info.EmbedColor.Value == defaultColor.Value)) { var c = await GetImageColorAsync(info.ThumbnailEmbeddableUrl, defaultColor).ConfigureAwait(false); if (c.HasValue && c.Value.Value != defaultColor.Value) { info.EmbedColor = c.Value.Value; await db.SaveChangesAsync(Config.Cts.Token).ConfigureAwait(false); } } var color = info.EmbedColor.HasValue ? new DiscordColor(info.EmbedColor.Value) : defaultColor; return(info.ThumbnailEmbeddableUrl, color); }
private static async Task UpdateGameTitlesAsync(CancellationToken cancellationToken) { var container = Path.GetFileName(TitleDownloadLink.AbsolutePath); try { if (ScrapeStateProvider.IsFresh(container)) { return; } Config.Log.Debug("Scraping GameTDB for game titles..."); using (var fileStream = new FileStream(Path.GetTempFileName(), FileMode.Create, FileAccess.ReadWrite, FileShare.Read, 16384, FileOptions.Asynchronous | FileOptions.RandomAccess | FileOptions.DeleteOnClose)) { using (var downloadStream = await HttpClient.GetStreamAsync(TitleDownloadLink).ConfigureAwait(false)) await downloadStream.CopyToAsync(fileStream, 16384, cancellationToken).ConfigureAwait(false); fileStream.Seek(0, SeekOrigin.Begin); using (var zipArchive = new ZipArchive(fileStream, ZipArchiveMode.Read)) { var logEntry = zipArchive.Entries.FirstOrDefault(e => e.Name.EndsWith(".xml", StringComparison.InvariantCultureIgnoreCase)); if (logEntry == null) { throw new InvalidOperationException("No zip entries that match the .xml criteria"); } using (var zipStream = logEntry.Open()) using (var xmlReader = XmlReader.Create(zipStream)) { xmlReader.ReadToFollowing("PS3TDB"); var version = xmlReader.GetAttribute("version"); if (!DateTime.TryParseExact(version, "yyyyMMddHHmmss", CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal, out var timestamp)) { return; } if (ScrapeStateProvider.IsFresh("PS3TDB", timestamp)) { await ScrapeStateProvider.SetLastRunTimestampAsync("PS3TDB").ConfigureAwait(false); return; } while (!cancellationToken.IsCancellationRequested && xmlReader.ReadToFollowing("game")) { if (xmlReader.ReadToFollowing("id")) { var productId = xmlReader.ReadElementContentAsString().ToUpperInvariant(); if (!ProductCodeLookup.ProductCode.IsMatch(productId)) { continue; } string title = null; if (xmlReader.ReadToFollowing("locale") && xmlReader.ReadToFollowing("title")) { title = xmlReader.ReadElementContentAsString(); } if (!string.IsNullOrEmpty(title)) { using (var db = new ThumbnailDb()) { var item = await db.Thumbnail.FirstOrDefaultAsync(t => t.ProductCode == productId, cancellationToken).ConfigureAwait(false); if (item == null) { await db.Thumbnail.AddAsync(new Thumbnail { ProductCode = productId, Name = title }, cancellationToken).ConfigureAwait(false); await db.SaveChangesAsync(cancellationToken).ConfigureAwait(false); } else { if (item.Name != title && item.Timestamp == 0) { item.Name = title; await db.SaveChangesAsync(cancellationToken).ConfigureAwait(false); } } } } } } await ScrapeStateProvider.SetLastRunTimestampAsync("PS3TDB").ConfigureAwait(false); } } } await ScrapeStateProvider.SetLastRunTimestampAsync(container).ConfigureAwait(false); } catch (Exception e) { PrintError(e); } finally { Config.Log.Debug("Finished scraping GameTDB for game titles"); } }