Struct EventDB

pub(crate) struct EventDB {}

The Catalyst Event SQL Database

Implementations

impl EventDB

const BALLOTS_VOTE_OPTIONS_PER_EVENT_QUERY: &'static str = "SELECT vote_options.objective, proposal.id as proposal_id, objective.id as objective_id FROM proposal INNER JOIN objective ON proposal.objective = objective.row_id INNER JOIN vote_options ON objective.vote_options = vote_options.id WHERE objective.event = $1;"

Ballot vote options per event query template

const BALLOTS_VOTE_OPTIONS_PER_OBJECTIVE_QUERY: &'static str = "SELECT vote_options.objective, proposal.id as proposal_id FROM proposal INNER JOIN objective ON proposal.objective = objective.row_id INNER JOIN vote_options ON objective.vote_options = vote_options.id WHERE objective.event = $1 AND objective.id = $2;"

Ballot vote options per objective query template

const BALLOT_VOTE_OPTIONS_QUERY: &'static str = "SELECT vote_options.objective FROM proposal INNER JOIN objective ON proposal.objective = objective.row_id INNER JOIN vote_options ON objective.vote_options = vote_options.id WHERE objective.event = $1 AND objective.id = $2 AND proposal.id = $3;"

Ballot vote options query template

const BALLOT_VOTE_PLANS_QUERY: &'static str = "SELECT proposal_voteplan.bb_proposal_index, voteplan.id, voteplan.category, voteplan.encryption_key, voteplan.group_id FROM proposal_voteplan INNER JOIN proposal ON proposal_voteplan.proposal_id = proposal.row_id INNER JOIN voteplan ON proposal_voteplan.voteplan_id = voteplan.row_id INNER JOIN objective ON proposal.objective = objective.row_id WHERE objective.event = $1 AND objective.id = $2 AND proposal.id = $3;"

Ballot vote plans query template

impl EventDB

pub(crate) async fn get_ballot( event: EventId, objective: ObjectiveId, proposal: ProposalId, ) -> Result<Ballot>

Get ballot query

pub(crate) async fn get_objective_ballots( &self, event: EventId, objective: ObjectiveId, ) -> Result<Vec<ProposalBallot>>

Get objective’s ballots query

pub(crate) async fn get_event_ballots( &self, event: EventId, ) -> Result<Vec<ObjectiveBallots>>

Get event’s ballots query

impl EventDB

const OBJECTIVES_QUERY: &'static str = "SELECT objective.row_id, objective.id, objective.title, objective.description, objective.deleted, objective.rewards_currency, objective.rewards_total, objective.extra, objective_category.name, objective_category.description as objective_category_description FROM objective INNER JOIN objective_category on objective.category = objective_category.name WHERE objective.event = $1 LIMIT $2 OFFSET $3;"

Objectives query template

const VOTING_GROUPS_QUERY: &'static str = "SELECT voteplan.group_id as group, voteplan.token_id as voting_token FROM voteplan WHERE objective_id = $1;"

Voting Groups query template

impl EventDB

pub(crate) async fn get_objectives( event: EventId, limit: Option<i64>, offset: Option<i64>, ) -> Result<Vec<Objective>>

Get objectives query

impl EventDB

const PROPOSALS_QUERY: &'static str = "SELECT proposal.id, proposal.title, proposal.summary, proposal.deleted FROM proposal INNER JOIN objective on proposal.objective = objective.row_id WHERE objective.event = $1 AND objective.id = $2 LIMIT $3 OFFSET $4;"

Proposals query template

const PROPOSAL_QUERY: &'static str = "SELECT proposal.id, proposal.title, proposal.summary, proposal.deleted, proposal.extra, proposal.funds, proposal.url, proposal.files_url, proposal.proposer_name, proposal.proposer_contact, proposal.proposer_url, proposal.public_key FROM proposal INNER JOIN objective on proposal.objective = objective.row_id WHERE objective.event = $1 AND objective.id = $2 AND proposal.id = $3;"

Proposal details query template

impl EventDB

pub(crate) async fn get_proposal( event: EventId, objective: ObjectiveId, proposal: ProposalId, ) -> Result<Proposal>

Get proposal query

pub(crate) async fn get_proposals( &self, event: EventId, objective: ObjectiveId, limit: Option<i64>, offset: Option<i64>, ) -> Result<Vec<ProposalSummary>>

Get proposals query

impl EventDB

const RATINGS_PER_REVIEW_QUERY: &'static str = "SELECT review_rating.metric, review_rating.rating, review_rating.note FROM review_rating WHERE review_rating.review_id = $1;"

Rating per review query template

const REVIEWS_QUERY: &'static str = "SELECT proposal_review.row_id, proposal_review.assessor FROM proposal_review INNER JOIN proposal on proposal.row_id = proposal_review.proposal_id INNER JOIN objective on proposal.objective = objective.row_id WHERE objective.event = $1 AND objective.id = $2 AND proposal.id = $3 LIMIT $4 OFFSET $5;"

Review query template

const REVIEW_TYPES_QUERY: &'static str = "SELECT review_metric.row_id, review_metric.name, review_metric.description, review_metric.min, review_metric.max, review_metric.map, objective_review_metric.note, objective_review_metric.review_group FROM review_metric INNER JOIN objective_review_metric on review_metric.row_id = objective_review_metric.metric INNER JOIN objective on objective_review_metric.objective = objective.row_id WHERE objective.event = $1 AND objective.id = $2 LIMIT $3 OFFSET $4;"

Review types query template

impl EventDB

pub(crate) async fn get_reviews( event: EventId, objective: ObjectiveId, proposal: ProposalId, limit: Option<i64>, offset: Option<i64>, ) -> Result<Vec<AdvisorReview>>

Get reviews query

pub(crate) async fn get_review_types( &self, event: EventId, objective: ObjectiveId, limit: Option<i64>, offset: Option<i64>, ) -> Result<Vec<ReviewType>>

Get review types query

impl EventDB

const EVENTS_QUERY: &'static str = "SELECT event.row_id, event.name, event.start_time, event.end_time, snapshot.last_updated FROM event LEFT JOIN snapshot ON event.row_id = snapshot.event ORDER BY event.row_id ASC LIMIT $1 OFFSET $2;"

Events query template

const EVENT_GOALS_QUERY: &'static str = "SELECT goal.idx, goal.name FROM goal WHERE goal.event_id = $1;"

Event goals query template

const EVENT_QUERY: &'static str = "SELECT event.row_id, event.name, event.start_time, event.end_time, event.snapshot_start, event.registration_snapshot_time, event.voting_power_threshold, event.max_voting_power_pct, event.insight_sharing_start, event.proposal_submission_start, event.refine_proposals_start, event.finalize_proposals_start, event.proposal_assessment_start, event.assessment_qa_start, event.voting_start, event.voting_end, event.tallying_end, snapshot.last_updated FROM event LEFT JOIN snapshot ON event.row_id = snapshot.event WHERE event.row_id = $1;"

Event details query template

impl EventDB

pub(crate) async fn get_events( &self, limit: Option<i64>, offset: Option<i64>, ) -> Result<Vec<EventSummary>>

Get events query

pub(crate) async fn get_event(event: EventId) -> Result<Event>

Get event query

impl EventDB

const DELEGATIONS_BY_EVENT_QUERY: &'static str = "SELECT contribution.voting_key, contribution.voting_group, contribution.voting_weight, contribution.value, contribution.reward_address FROM contribution INNER JOIN snapshot ON contribution.snapshot_id = snapshot.row_id WHERE contribution.stake_public_key = $1 AND snapshot.event = $2;"

Delegations by event query template

const DELEGATOR_SNAPSHOT_INFO_BY_EVENT_QUERY: &'static str = " SELECT snapshot.as_at, snapshot.last_updated, snapshot.final FROM snapshot WHERE snapshot.event = $1 LIMIT 1;"

Delegator snapshot info by event query template

const DELEGATOR_SNAPSHOT_INFO_BY_LAST_EVENT_QUERY: &'static str = "SELECT snapshot.event, snapshot.as_at, snapshot.last_updated, snapshot.final FROM snapshot WHERE snapshot.last_updated = (SELECT MAX(snapshot.last_updated) as last_updated from snapshot) LIMIT 1;"

Delegator snapshot info by last event query template

const TOTAL_BY_EVENT_VOTING_QUERY: &'static str = "SELECT SUM(voter.voting_power)::BIGINT as total_voting_power FROM voter INNER JOIN snapshot ON voter.snapshot_id = snapshot.row_id WHERE voter.voting_group = $1 AND snapshot.event = $2;"

Total By Event Query template

const TOTAL_BY_LAST_EVENT_VOTING_QUERY: &'static str = "SELECT SUM(voter.voting_power)::BIGINT as total_voting_power FROM voter INNER JOIN snapshot ON voter.snapshot_id = snapshot.row_id AND snapshot.last_updated = (SELECT MAX(snapshot.last_updated) as last_updated from snapshot) WHERE voter.voting_group = $1;"

Total By Last Event Query template

const TOTAL_POWER_BY_EVENT_QUERY: &'static str = "SELECT SUM(voter.voting_power)::BIGINT as total_voting_power FROM voter INNER JOIN snapshot ON voter.snapshot_id = snapshot.row_id WHERE snapshot.event = $1;"

Total voting power by event query template

const TOTAL_POWER_BY_LAST_EVENT_QUERY: &'static str = "SELECT SUM(voter.voting_power)::BIGINT as total_voting_power FROM voter INNER JOIN snapshot ON voter.snapshot_id = snapshot.row_id WHERE snapshot.last_updated = (SELECT MAX(snapshot.last_updated) as last_updated from snapshot);"

Total voting power by last event query template

const VOTER_BY_EVENT_QUERY: &'static str = "SELECT voter.voting_key, voter.voting_group, voter.voting_power, snapshot.as_at, snapshot.last_updated, snapshot.final, SUM(contribution.value)::BIGINT as delegations_power, COUNT(contribution.value) AS delegations_count FROM voter INNER JOIN snapshot ON voter.snapshot_id = snapshot.row_id INNER JOIN contribution ON contribution.snapshot_id = snapshot.row_id WHERE voter.voting_key = $1 AND contribution.voting_key = $1 AND snapshot.event = $2 GROUP BY voter.voting_key, voter.voting_group, voter.voting_power, snapshot.as_at, snapshot.last_updated, snapshot.final;"

Voter By Event Query template

const VOTER_BY_LAST_EVENT_QUERY: &'static str = "SELECT snapshot.event, voter.voting_key, voter.voting_group, voter.voting_power, snapshot.as_at, snapshot.last_updated, snapshot.final, SUM(contribution.value)::BIGINT as delegations_power, COUNT(contribution.value) AS delegations_count FROM voter INNER JOIN snapshot ON voter.snapshot_id = snapshot.row_id INNER JOIN contribution ON contribution.snapshot_id = snapshot.row_id WHERE voter.voting_key = $1 AND contribution.voting_key = $1 AND snapshot.last_updated = (SELECT MAX(snapshot.last_updated) as last_updated from snapshot) GROUP BY snapshot.event, voter.voting_key, voter.voting_group, voter.voting_power, snapshot.as_at, snapshot.last_updated, snapshot.final;"

Voter By Last Event Query template

const VOTER_DELEGATORS_LIST_QUERY: &'static str = "SELECT contribution.stake_public_key FROM contribution INNER JOIN snapshot ON contribution.snapshot_id = snapshot.row_id WHERE contribution.voting_key = $1 AND snapshot.event = $2;"

Voter Delegators List Query template

impl EventDB

pub(crate) async fn get_voter( event: &Option<EventId>, voting_key: String, with_delegations: bool, ) -> Result<Voter>

Get voter query

pub(crate) async fn get_delegator( event: &Option<EventId>, stake_public_key: String, ) -> Result<Delegator>

Get delegator query

impl EventDB

const SEARCH_EVENTS_QUERY: &'static str = "SELECT event.row_id, event.name, event.start_time, event.end_time, snapshot.last_updated FROM event LEFT JOIN snapshot ON event.row_id = snapshot.event"

Search for events query template

const SEARCH_OBJECTIVES_QUERY: &'static str = "SELECT objective.id, objective.title, objective.description, objective.deleted, objective_category.name, objective_category.description as objective_category_description FROM objective INNER JOIN objective_category on objective.category = objective_category.name"

Search for objectives query template

const SEARCH_PROPOSALS_QUERY: &'static str = "SELECT DISTINCT proposal.id, proposal.title, proposal.summary, proposal.deleted FROM proposal"

Search for proposals query template

fn build_where_clause(table: &str, filter: &[SearchConstraint]) -> String

Build a where clause

fn build_order_by_clause(table: &str, order_by: &[SearchOrderBy]) -> String

Build an order by clause

fn construct_query(search_query: &SearchQuery) -> String

Construct a search query

fn construct_count_query(search_query: &SearchQuery) -> String

Construct a count query

async fn search_total( search_query: SearchQuery, limit: Option<i64>, offset: Option<i64>, ) -> Result<SearchResult>

Search for a total.

async fn search_events( search_query: SearchQuery, limit: Option<i64>, offset: Option<i64>, ) -> Result<SearchResult>

Search for events

async fn search_objectives( &self, search_query: SearchQuery, limit: Option<i64>, offset: Option<i64>, ) -> Result<SearchResult>

Search for objectives

async fn search_proposals( &self, search_query: SearchQuery, limit: Option<i64>, offset: Option<i64>, ) -> Result<SearchResult>

Search for proposals

impl EventDB

pub(crate) async fn search( &self, search_query: SearchQuery, total: bool, limit: Option<i64>, offset: Option<i64>, ) -> Result<SearchResult>

Search query

impl EventDB

const FUND_CHALLENGES_QUERY: &'static str = "SELECT objective.row_id AS internal_id, objective.id AS id, objective.category AS challenge_type, objective.title AS title, objective.description AS description, objective.rewards_total AS rewards_total, objective.proposers_rewards AS proposers_rewards, (objective.extra->'url') #>> '{}' AS challenge_url, (objective.extra->'sponsor') #>> '{}' AS highlights FROM objective WHERE objective.event = $1;"

const FUND_GOALS_QUERY: &'static str = "SELECT id, name AS goal_name FROM goal WHERE goal.event_id = $1;"

const FUND_GROUPS_QUERY: &'static str = "SELECT voteplan.token_id AS token_identifier, voting_group.name AS group_id FROM voting_group INNER JOIN voteplan ON voteplan.group_id = voting_group.name INNER JOIN objective ON voteplan.objective_id = objective.row_id WHERE objective.event = $1;"

const FUND_QUERY: &'static str = "SELECT this_fund.row_id AS id, this_fund.name AS fund_name, this_fund.description AS fund_goal, this_fund.registration_snapshot_time AS registration_snapshot_time, this_fund.voting_power_threshold AS voting_power_threshold, this_fund.start_time AS fund_start_time, this_fund.end_time AS fund_end_time, this_fund.insight_sharing_start AS insight_sharing_start, this_fund.proposal_submission_start AS proposal_submission_start, this_fund.refine_proposals_start AS refine_proposals_start, this_fund.finalize_proposals_start AS finalize_proposals_start, this_fund.proposal_assessment_start AS proposal_assessment_start, this_fund.assessment_qa_start AS assessment_qa_start, this_fund.snapshot_start AS snapshot_start, this_fund.voting_start AS voting_start, this_fund.voting_end AS voting_end, this_fund.tallying_end AS tallying_end, (this_fund.extra->'url'->'results') #>> '{}' AS results_url, (this_fund.extra->'url'->'survey') #>> '{}' AS survey_url, next_fund.row_id AS next_id, next_fund.start_time AS next_fund_start_time, next_fund.registration_snapshot_time AS next_registration_snapshot_time, next_fund.name AS next_fund_name, next_fund.insight_sharing_start AS next_insight_sharing_start, next_fund.proposal_submission_start AS next_proposal_submission_start, next_fund.refine_proposals_start AS next_refine_proposals_start, next_fund.finalize_proposals_start AS next_finalize_proposals_start, next_fund.proposal_assessment_start AS next_proposal_assessment_start, next_fund.assessment_qa_start AS next_assessment_qa_start, next_fund.snapshot_start AS next_snapshot_start, next_fund.voting_start AS next_voting_start, next_fund.voting_end AS next_voting_end, next_fund.tallying_end AS next_tallying_end FROM event this_fund LEFT JOIN event next_fund ON next_fund.row_id = this_fund.row_id + 1 WHERE this_fund.end_time > CURRENT_TIMESTAMP AT TIME ZONE 'UTC' AND this_fund.start_time < CURRENT_TIMESTAMP AT TIME ZONE 'UTC' ORDER BY this_fund.row_id DESC LIMIT 1;"

const FUND_VOTE_PLANS_QUERY: &'static str = "SELECT voteplan.row_id AS id, voteplan.id AS chain_voteplan_id, voteplan.category AS chain_voteplan_payload, voteplan.encryption_key AS chain_vote_encryption_key, event.row_id AS fund_id, voteplan.token_id AS token_identifier FROM voteplan INNER JOIN objective ON voteplan.objective_id = objective.row_id INNER JOIN event ON objective.event = event.row_id WHERE event.row_id = $1;"

impl EventDB

pub(crate) async fn get_fund() -> Result<FundWithNext>

Get fund query

impl EventDB

pub(crate) async fn schema_version_check() -> Result<i32>

Check the schema version. return the current schema version if its current. Otherwise return an error.

impl EventDB

pub(crate) fn is_deep_query_enabled() -> bool

Determine if deep query inspection is enabled.

pub(crate) fn modify_deep_query(enable: bool)

Modify the deep query inspection setting.

Arguments

• enable - Set the DeepQueryInspection setting to this value.

pub(crate) async fn query( stmt: &str, params: &[&(dyn ToSql + Sync)], ) -> Result<Vec<Row>>

Query the database.

If deep query inspection is enabled, this will log the query plan inside a rolled-back transaction, before running the query.

Arguments

• stmt - &str SQL statement.
• params - &[&(dyn ToSql + Sync)] SQL parameters.

Returns

anyhow::Result<Vec<Row>>

pub(crate) async fn query_stream( stmt: &str, params: &[&(dyn ToSql + Sync)], ) -> Result<impl Stream<Item = Result<Row>>>

Query the database and return a async stream of rows.

If deep query inspection is enabled, this will log the query plan inside a rolled-back transaction, before running the query.

Arguments

• stmt - &str SQL statement.
• params - &[&(dyn ToSql + Sync)] SQL parameters.

Returns

anyhow::Result<impl Stream<Item = anyhow::Result<Row>>>

pub(crate) async fn query_one( stmt: &str, params: &[&(dyn ToSql + Sync)], ) -> Result<Row>

Query the database for a single row.

Arguments

• stmt - &str SQL statement.
• params - &[&(dyn ToSql + Sync)] SQL parameters.

Returns

Result<Row, anyhow::Error>

pub(crate) async fn modify( stmt: &str, params: &[&(dyn ToSql + Sync)], ) -> Result<()>

Modify the database.

Use this for UPDATE, DELETE, and other DB statements that don’t return data.

Arguments

• stmt - &str SQL statement.
• params - &[&(dyn ToSql + Sync)] SQL parameters.

Returns

anyhow::Result<()>

async fn explain_analyze_rollback( stmt: &str, params: &[&(dyn ToSql + Sync)], ) -> Result<()>

Prepend EXPLAIN ANALYZE to the query, and rollback the transaction.

async fn explain_analyze_commit( stmt: &str, params: &[&(dyn ToSql + Sync)], ) -> Result<()>

Prepend EXPLAIN ANALYZE to the query, and commit the transaction.

async fn explain_analyze( stmt: &str, params: &[&(dyn ToSql + Sync)], rollback: bool, ) -> Result<()>

Prepend EXPLAIN ANALYZE to the query.

Log the query plan inside a transaction that may be committed or rolled back.

Arguments

• stmt - &str SQL statement.
• params - &[&(dyn ToSql + Sync)] SQL parameters.
• rollback - bool whether to roll back the transaction or not.