public IAsyncEnumerable <TeamUpdateView> GetTeamUpdates(TeamUpdateQueryOpts opts)
{
var q = new SqlKata.Query("team_versions")
.ApplySorting(opts, "first_seen", "update_id")
.ApplyBounds(opts, "first_seen");
if (opts.Teams != null)
{
q.WhereIn("team_id", opts.Teams);
}
return(_db.QueryKataAsync <TeamUpdateView>(q));
}
private IAsyncEnumerable <TributesUpdateView> GetTributeUpdatesInner(string table, TributesQuery opts)
{
var q = new SqlKata.Query(table)
.SelectRaw("update_id, timestamp, array_agg(player_id) as players, array_agg(peanuts) as peanuts")
.GroupBy("timestamp", "update_id")
.ApplySorting(opts, "timestamp", "update_id")
.ApplyBounds(opts, "timestamp");
if (opts.Players != null)
{
q.WhereIn("player_id", opts.Players);
}
return(_db.QueryKataAsync <TributesUpdateView>(q));
}
public IAsyncEnumerable <GameUpdateView> GetGameUpdates(GameUpdateQueryOptions opts, bool ignoreSort = false)
{
var q = new SqlKata.Query("game_updates_unique")
.Select("game_id", "timestamp", "hash", "data")
.ApplyBounds(opts, "timestamp");
if (!ignoreSort)
{
q.ApplySorting(opts, "timestamp", "hash");
}
if (opts.Season != null)
{
q.Where("season", opts.Season.Value);
}
if (opts.Tournament != null)
{
q.Where("tournament", opts.Tournament.Value);
}
if (opts.Day != null)
{
q.Where("day", opts.Day.Value);
}
if (opts.Game != null)
{
q.WhereIn("game_id", opts.Game);
}
if (opts.Search != null)
{
q.WhereRaw("search_tsv @@ websearch_to_tsquery(?)", opts.Search);
}
if (opts.Started != null)
{
q.WhereRaw("(data->>'gameStart')::bool = ?", opts.Started.Value);
}
return(_db.QueryKataAsync <GameUpdateView>(q));
}
public IEnumerable <Entities.Product> GetProducts()
{
var query = new SqlKata.Query("GamePieces")
.Select("GamePieces.releaseKey AS ReleaseKey",
"GamePieces.value AS Title")
.Join("GamePieceTypes", "GamePieces.gamePieceTypeId", "GamePieceTypes.id")
.WhereIn("releaseKey",
new SqlKata.Query("InstalledExternalProducts")
.SelectRaw("platforms.name || '_' || InstalledExternalProducts.productId")
.Join("Platforms", "InstalledExternalProducts.platformId", "Platforms.id")
.Union(
new SqlKata.Query("InstalledProducts")
.SelectRaw("'gog_' || productId")
)
)
.Where("GamePieceTypes.type", "originalTitle");
return(Query <Entities.Product>(query).Select(product =>
{
product.Title = product.Title.Match("{\"title\":\"(.*)\"}").Groups[1].Value;
return product;
}));
}
protected static IEnumerable <T> Query <T>(SqlKata.Query query)
{
var sql = Compiler.Compile(query);
return(Connection.Query <T>(sql.Sql, sql.NamedBindings));
}
public KataBaseQuery(k.Query kataQuery) : base(kataQuery)
{
}
public KataBase(k.Query kataQuery)
{
this.KataQuery = kataQuery;
}
public static async IAsyncEnumerable <T> QueryKataAsync <T>(this Database db, SqlKata.Query query)
{
await using var conn = await db.Obtain();
await foreach (var value in conn.QueryKataAsync <T>(query))
{
yield return(value);
}
}
public static async IAsyncEnumerable <T> QueryKataAsync <T>(this NpgsqlConnection conn, SqlKata.Query query)
{
var compiled = new PostgresCompiler().Compile(query);
await using var reader = await conn.ExecuteReaderAsync(compiled.Sql, compiled.NamedBindings);
var parser = reader.GetRowParser <T>();
while (await reader.ReadAsync())
{
yield return(parser(reader));
}
}
