public async Task <IEnumerable <BallotOperation> > GetBallots(
AccountParameter sender,
Int32Parameter level,
DateTimeParameter timestamp,
Int32Parameter epoch,
Int32Parameter period,
ProtocolParameter proposal,
SortParameter sort,
OffsetParameter offset,
int limit,
Symbols quote)
{
var sql = new SqlBuilder(@"
SELECT o.""Id"", o.""Level"", o.""Timestamp"", o.""OpHash"", o.""SenderId"", o.""Rolls"", o.""Vote"", o.""Epoch"", o.""Period"",
b.""Hash"",
proposal.""Hash"" as ""ProposalHash"", proposal.""Metadata"" ->> 'alias' as ""ProposalAlias"",
period.""Kind"", period.""FirstLevel"", period.""LastLevel""
FROM ""BallotOps"" as o
INNER JOIN ""Blocks"" as b ON b.""Level"" = o.""Level""
INNER JOIN ""Proposals"" as proposal ON proposal.""Id"" = o.""ProposalId""
INNER JOIN ""VotingPeriods"" as period ON period.""Index"" = o.""Period""
")
.Filter("SenderId", sender)
.FilterA(@"o.""Level""", level)
.FilterA(@"o.""Timestamp""", timestamp)
.FilterA(@"o.""Epoch""", epoch)
.FilterA(@"o.""Period""", period)
.FilterA(@"proposal.""Hash""", proposal)
.Take(sort, offset, limit, x => x == "level" ? ("Id", "Level") : ("Id", "Id"), "o");
using var db = GetConnection();
var rows = await db.QueryAsync(sql.Query, sql.Params);
return(rows.Select(row => new BallotOperation
{
Id = row.Id,
Level = row.Level,
Block = row.Hash,
Timestamp = row.Timestamp,
Hash = row.OpHash,
Period = new PeriodInfo
{
Index = row.Period,
Epoch = row.Epoch,
Kind = PeriodKinds.ToString(row.Kind),
FirstLevel = row.FirstLevel,
LastLevel = row.LastLevel
},
Proposal = new ProposalAlias
{
Hash = row.ProposalHash,
Alias = row.ProposalAlias
},
Delegate = Accounts.GetAlias(row.SenderId),
Rolls = row.Rolls,
Vote = Votes.ToString(row.Vote),
Quote = Quotes.Get(quote, row.Level)
}));
}
public async Task <IEnumerable <ProposalOperation> > GetProposals(string hash, Symbols quote)
{
var sql = @"
SELECT o.""Id"", o.""Level"", o.""Timestamp"", o.""SenderId"", o.""Rolls"", o.""Duplicated"", o.""Epoch"", o.""Period"",
b.""Hash"",
proposal.""Hash"" as ""ProposalHash"", proposal.""Metadata"" ->> 'alias' as ""ProposalAlias"",
period.""Kind"", period.""FirstLevel"", period.""LastLevel""
FROM ""ProposalOps"" as o
INNER JOIN ""Blocks"" as b
ON b.""Level"" = o.""Level""
INNER JOIN ""Proposals"" as proposal
ON proposal.""Id"" = o.""ProposalId""
INNER JOIN ""VotingPeriods"" as period
ON period.""Index"" = o.""Period""
WHERE o.""OpHash"" = @hash::character(51)
ORDER BY o.""Id""";
using var db = GetConnection();
var rows = await db.QueryAsync(sql, new { hash });
return(rows.Select(row => new ProposalOperation
{
Id = row.Id,
Level = row.Level,
Block = row.Hash,
Timestamp = row.Timestamp,
Hash = hash,
Rolls = row.Rolls,
Duplicated = row.Duplicated,
Period = new PeriodInfo
{
Index = row.Period,
Epoch = row.Epoch,
Kind = PeriodKinds.ToString(row.Kind),
FirstLevel = row.FirstLevel,
LastLevel = row.LastLevel
},
Proposal = new ProposalAlias
{
Hash = row.ProposalHash,
Alias = row.ProposalAlias
},
Delegate = Accounts.GetAlias(row.SenderId),
Quote = Quotes.Get(quote, row.Level)
}));
}
public async Task <object[]> GetProposals(
AccountParameter sender,
Int32Parameter level,
DateTimeParameter timestamp,
Int32Parameter epoch,
Int32Parameter period,
ProtocolParameter proposal,
BoolParameter duplicated,
SortParameter sort,
OffsetParameter offset,
int limit,
string field,
Symbols quote)
{
var columns = new HashSet <string>(4);
var joins = new HashSet <string>(3);
switch (field)
{
case "id": columns.Add(@"o.""Id"""); break;
case "level": columns.Add(@"o.""Level"""); break;
case "timestamp": columns.Add(@"o.""Timestamp"""); break;
case "hash": columns.Add(@"o.""OpHash"""); break;
case "delegate": columns.Add(@"o.""SenderId"""); break;
case "rolls": columns.Add(@"o.""Rolls"""); break;
case "duplicated": columns.Add(@"o.""Duplicated"""); break;
case "proposal":
columns.Add(@"proposal.""Hash"" as ""ProposalHash""");
columns.Add(@"proposal.""Metadata"" ->> 'alias' as ""ProposalAlias""");
joins.Add(@"INNER JOIN ""Proposals"" as proposal ON proposal.""Id"" = o.""ProposalId""");
break;
case "period":
columns.Add(@"o.""Epoch""");
columns.Add(@"o.""Period""");
columns.Add(@"period.""Kind""");
columns.Add(@"period.""FirstLevel""");
columns.Add(@"period.""LastLevel""");
joins.Add(@"INNER JOIN ""VotingPeriods"" as period ON period.""Index"" = o.""Period""");
break;
case "block":
columns.Add(@"b.""Hash""");
joins.Add(@"INNER JOIN ""Blocks"" as b ON b.""Level"" = o.""Level""");
break;
case "quote": columns.Add(@"o.""Level"""); break;
}
if (period != null)
{
joins.Add(@"INNER JOIN ""VotingPeriods"" as period ON period.""Index"" = o.""Period""");
}
if (proposal != null)
{
joins.Add(@"INNER JOIN ""Proposals"" as proposal ON proposal.""Id"" = o.""ProposalId""");
}
if (columns.Count == 0)
{
return(Array.Empty <object>());
}
var sql = new SqlBuilder($@"SELECT {string.Join(',', columns)} FROM ""ProposalOps"" as o {string.Join(' ', joins)}")
.Filter("SenderId", sender)
.FilterA(@"o.""Level""", level)
.FilterA(@"o.""Timestamp""", timestamp)
.FilterA(@"o.""Duplicated""", duplicated)
.FilterA(@"o.""Epoch""", epoch)
.FilterA(@"o.""Period""", period)
.FilterA(@"proposal.""Hash""", proposal)
.Take(sort, offset, limit, x => x == "level" ? ("Id", "Level") : ("Id", "Id"), "o");
using var db = GetConnection();
var rows = await db.QueryAsync(sql.Query, sql.Params);
//TODO: optimize memory allocation
var result = new object[rows.Count()];
var j = 0;
switch (field)
{
case "id":
foreach (var row in rows)
{
result[j++] = row.Id;
}
break;
case "level":
foreach (var row in rows)
{
result[j++] = row.Level;
}
break;
case "block":
foreach (var row in rows)
{
result[j++] = row.Hash;
}
break;
case "timestamp":
foreach (var row in rows)
{
result[j++] = row.Timestamp;
}
break;
case "hash":
foreach (var row in rows)
{
result[j++] = row.OpHash;
}
break;
case "rolls":
foreach (var row in rows)
{
result[j++] = row.Rolls;
}
break;
case "duplicated":
foreach (var row in rows)
{
result[j++] = row.Duplicated;
}
break;
case "period":
foreach (var row in rows)
{
result[j++] = new PeriodInfo
{
Index = row.Period,
Epoch = row.Epoch,
Kind = PeriodKinds.ToString(row.Kind),
FirstLevel = row.FirstLevel,
LastLevel = row.LastLevel
}
}
;
break;
case "proposal":
foreach (var row in rows)
{
result[j++] = new ProposalAlias
{
Hash = row.ProposalHash,
Alias = row.ProposalAlias
}
}
;
break;
case "delegate":
foreach (var row in rows)
{
result[j++] = await Accounts.GetAliasAsync(row.SenderId);
}
break;
case "quote":
foreach (var row in rows)
{
result[j++] = Quotes.Get(quote, row.Level);
}
break;
}
return(result);
}
