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");
}
}
