-- -----------------------------------------------------

-- roles

-- -----------------------------------------------------

-- tzkt

DROP ROLE IF EXISTS indexer;

CREATE ROLE indexer NOINHERIT;

GRANT indexer to tzkt;

-- postgrest & hasura

DROP ROLE IF EXISTS api_views_owner;

DROP ROLE IF EXISTS api_anon;

CREATE ROLE api_views_owner NOINHERIT;

CREATE ROLE api_anon NOINHERIT;

GRANT api_views_owner to tzkt;

GRANT api_anon to postgrest;

GRANT api_anon to hasura;

DROP SCHEMA IF EXISTS hdb_catalog CASCADE;

DROP SCHEMA IF EXISTS hdb_views CASCADE;

CREATE SCHEMA hdb_catalog;

CREATE SCHEMA hdb_views;

ALTER SCHEMA hdb_catalog OWNER TO hasura;

ALTER SCHEMA hdb_views OWNER TO hasura;

GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO hasura;

GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO hasura;

-- -----------------------------------------------------

-- schemas

-- -----------------------------------------------------

-- extensions

CREATE EXTENSION IF NOT EXISTS pgcrypto;

ALTER EXTENSION pgcrypto SET SCHEMA public;

GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO hasura;

-- public

REVOKE ALL ON SCHEMA public FROM PUBLIC;

GRANT USAGE ON SCHEMA public TO indexer;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO indexer;

GRANT USAGE ON SCHEMA public TO api_views_owner;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO api_views_owner;

GRANT USAGE ON SCHEMA public TO hasura;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO hasura;

-- api (postgrest)

DROP SCHEMA IF EXISTS api CASCADE;

CREATE SCHEMA api;

-- -----------------------------------------------------

-- functions

-- -----------------------------------------------------

-- ensure privileges to EXECUTE functions must be granted explicitly

ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

-- -----------------------------------------------------

-- tables

-- -----------------------------------------------------

-- ...

-- -----------------------------------------------------

-- views

-- -----------------------------------------------------

-- -----------------------

-- users

-- -----------------------

-- create view

CREATE OR REPLACE VIEW "api"."users" AS

SELECT

"accounts"."Address" AS "address",

"accounts"."FirstLevel" AS "first_level",

"accounts"."LastLevel" AS "last_level",

"accounts"."Balance" AS "balance",

"accounts"."Counter" AS "counter",

"accounts"."ContractsCount" AS "num_contracts",

"accounts"."DelegationsCount" AS "num_delegations",

"accounts"."OriginationsCount" AS "num_originations",

"accounts"."TransactionsCount" AS "num_transactions",

"accounts"."RevealsCount" AS "num_reveals",

"accounts"."MigrationsCount" AS "num_migrations",

"accounts"."DelegateId" AS "delegate_id",

"delegate"."Address" AS "delegate",

"accounts"."DelegationLevel" AS "delegation_level",

"accounts"."Staked" AS "is_staked",

"accounts"."PublicKey" AS "public_key"

FROM "public"."Accounts" AS "accounts"

LEFT JOIN "public"."Accounts" AS "delegate" ON "delegate"."Id" = "accounts"."DelegateId"

WHERE "accounts"."Type" = 0;

-- add view comments

COMMENT ON view "api"."users" IS 'User

Implicit accounts (tz) that are not registered as delegates.';

COMMENT ON column "api"."users"."address" IS 'Public key hash of the account';

COMMENT ON column "api"."users"."first_level" IS 'Block height of the first operation related to the account';

COMMENT ON column "api"."users"."last_level" IS 'Height of the block in which the account state was changed last time';

COMMENT ON column "api"."users"."balance" IS 'Account balance/Total balance of the delegate (baker), including spendable and frozen funds (micro tez)';

COMMENT ON column "api"."users"."counter" IS 'An account nonce which is used to prevent operation replay';

COMMENT ON column "api"."users"."num_contracts" IS 'Number of contracts, created (originated) and/or managed by the account';

COMMENT ON column "api"."users"."num_delegations" IS 'Number of delegation operations, related to the account';

COMMENT ON column "api"."users"."num_originations" IS 'Number of all origination (deployment / contract creation) operations, related to the account';

COMMENT ON column "api"."users"."num_transactions" IS 'Number of all transaction (tez transfer) operations, related to the account';

COMMENT ON column "api"."users"."num_reveals" IS 'Number of reveal (is used to reveal the public key associated with an account) operations of the contract';

COMMENT ON column "api"."users"."num_migrations" IS 'Number of migration (result of the context (database) migration during a protocol update) operations, related to the account (synthetic type)';

COMMENT ON column "api"."users"."delegate" IS 'Address of the current delegate of the account. null if it''s not delegated';

COMMENT ON column "api"."users"."delegation_level" IS 'Block height of latest delegation. null if it''s not delegated';

COMMENT ON column "api"."users"."is_staked" IS 'Whether account participates in staking (bakes or delegates)';

COMMENT ON column "api"."users"."public_key" IS 'Base58 representation of account''s public key, revealed by the account';

-- set view owner

ALTER VIEW "api"."users" OWNER TO "api_views_owner";

-- test

-- SELECT * FROM "api"."users";

-- -----------------------

-- delegates

-- -----------------------

CREATE OR REPLACE VIEW "api"."delegates" AS

SELECT

"accounts"."Address" AS "address",

"accounts"."FirstLevel" AS "first_level",

"accounts"."LastLevel" AS "last_level",

"accounts"."Balance" AS "balance",

"accounts"."Counter" AS "counter",

"accounts"."ContractsCount" AS "num_contracts",

"accounts"."DelegationsCount" AS "num_delegations",

"accounts"."OriginationsCount" AS "num_originations",

"accounts"."TransactionsCount" AS "num_transactions",

"accounts"."RevealsCount" AS "num_reveals",

"accounts"."MigrationsCount" AS "num_migrations",

"accounts"."Staked" AS "is_staked",

"accounts"."Activated" AS "is_activated",

"accounts"."Revealed" AS "revealed",

"accounts"."ActivationLevel" AS "activation_level",

"accounts"."DeactivationLevel" AS "deactivation_level",

"accounts"."FrozenDeposits" AS "frozen_deposits",

"accounts"."FrozenRewards" AS "frozen_rewards",

"accounts"."FrozenFees" AS "frozen_fees",

"accounts"."DelegatorsCount" AS "delegators_count",

"accounts"."StakingBalance" AS "staking_balance",

"accounts"."BlocksCount" AS "num_blocks",

"accounts"."EndorsementsCount" AS "num_endorsements",

"accounts"."BallotsCount" AS "num_ballots",

"accounts"."ProposalsCount" AS "num_proposals",

"accounts"."DoubleBakingCount" AS "num_double_bakings",

"accounts"."DoubleEndorsingCount" AS "num_double_endorsings",

"accounts"."NonceRevelationsCount" AS "num_nonce_revelations",

"accounts"."RevelationPenaltiesCount" AS "num_revelation_penalties"

FROM "public"."Accounts" AS "accounts"

WHERE "accounts"."Type" = 1;

-- add view comments

COMMENT ON view "api"."delegates" IS 'Delegate

Implicit accounts (tz) that are registered as delegates';

COMMENT ON column "api"."delegates"."address" IS 'Public key hash of the account';

COMMENT ON column "api"."delegates"."first_level" IS 'Block height of the first operation related to the account';

COMMENT ON column "api"."delegates"."last_level" IS 'Height of the block in which the account state was changed last time';

COMMENT ON column "api"."delegates"."balance" IS 'Account balance/Total balance of the delegate (baker), including spendable and frozen funds (micro tez)';

COMMENT ON column "api"."delegates"."counter" IS 'An account nonce which is used to prevent operation replay';

COMMENT ON column "api"."delegates"."num_contracts" IS 'Number of contracts, created (originated) and/or managed by the account';

COMMENT ON column "api"."delegates"."num_delegations" IS 'Number of delegation operations, related to the account';

COMMENT ON column "api"."delegates"."num_originations" IS 'Number of all origination (deployment / contract creation) operations, related to the account';

COMMENT ON column "api"."delegates"."num_transactions" IS 'Number of all transaction (tez transfer) operations, related to the account';

COMMENT ON column "api"."delegates"."num_reveals" IS 'Number of reveal (is used to reveal the public key associated with an account) operations of the contract';

COMMENT ON column "api"."delegates"."num_migrations" IS 'Number of migration (result of the context (database) migration during a protocol update) operations, related to the account (synthetic type)';

COMMENT ON column "api"."delegates"."is_staked" IS 'Whether account participates in staking (bakes or delegates)';

COMMENT ON column "api"."delegates"."is_activated" IS 'Delegation status (true - active, false - deactivated)';

COMMENT ON column "api"."delegates"."revealed" IS 'Public key revelation status. Unrevealed account can''t send manager operation (transaction, origination etc.)';

COMMENT ON column "api"."delegates"."activation_level" IS 'Block height when delegate (baker) was registered as a baker last time';

COMMENT ON column "api"."delegates"."deactivation_level" IS 'Block height when delegate (baker) was deactivated as a baker because of lack of funds or inactivity';

COMMENT ON column "api"."delegates"."frozen_deposits" IS 'Amount of security deposit, currently locked for baked (produced) blocks and (or) given endorsements (micro tez)';

COMMENT ON column "api"."delegates"."frozen_rewards" IS 'Amount of currently frozen baking rewards (micro tez)';

COMMENT ON column "api"."delegates"."frozen_fees" IS 'Amount of currently frozen fees paid by operations inside blocks, baked (produced) by the delegate (micro tez)';

COMMENT ON column "api"."delegates"."delegators_count" IS 'Number of current delegators (accounts, delegated their funds) of the delegate (baker)';

COMMENT ON column "api"."delegates"."staking_balance" IS 'Sum of delegate (baker) balance and delegated funds minus frozen rewards (micro tez)';

COMMENT ON column "api"."delegates"."num_blocks" IS 'Number of baked (validated) blocks all the time by the delegate (baker)';

COMMENT ON column "api"."delegates"."num_endorsements" IS 'Number of given endorsements (approvals) by the delegate (baker)';

COMMENT ON column "api"."delegates"."num_ballots" IS 'Number of submitted by the delegate ballots during a voting period';

COMMENT ON column "api"."delegates"."num_proposals" IS 'Number of submitted (upvoted) by the delegate proposals during a proposal period';

COMMENT ON column "api"."delegates"."num_double_bakings" IS 'Number of double baking (baking two different blocks at the same height) evidence operations, included in blocks, baked (validated) by the delegate';

COMMENT ON column "api"."delegates"."num_double_endorsings" IS 'Number of double endorsement (endorsing two different blocks at the same block height) evidence operations, included in blocks, baked (validated) by the delegate';

COMMENT ON column "api"."delegates"."num_nonce_revelations" IS 'Number of seed nonce revelation (are used by the blockchain to create randomness) operations provided by the delegate';

COMMENT ON column "api"."delegates"."num_revelation_penalties" IS 'Number of operations for all time in which rewards were lost due to unrevealed seed nonces by the delegate (synthetic type)';

-- set view owner

ALTER VIEW "api"."delegates" OWNER TO "api_views_owner";

-- test

-- SELECT * FROM "api"."delegates";

-- -----------------------

-- contracts

-- -----------------------

CREATE OR REPLACE VIEW "api"."contracts" AS

SELECT

"accounts"."Address" AS "address",

"accounts"."FirstLevel" AS "first_level",

"accounts"."LastLevel" AS "last_level",

"accounts"."Balance" AS "balance",

"accounts"."Counter" AS "counter",

"accounts"."ContractsCount" AS "num_contracts",

"accounts"."DelegationsCount" AS "num_delegations",

"accounts"."OriginationsCount" AS "num_originations",

"accounts"."TransactionsCount" AS "num_transactions",

"accounts"."RevealsCount" AS "num_reveals",

"accounts"."MigrationsCount" AS "num_migrations",

"accounts"."DelegateId" AS "delegate_id",

"delegate"."Address" AS "delegate",

"accounts"."DelegationLevel" AS "delegation_level",

"accounts"."Staked" AS "is_staked",

CASE

WHEN "accounts"."Kind" = 0 THEN 'delegator_contract'

WHEN "accounts"."Kind" = 1 THEN 'smart_contract'

ELSE NULL

END AS "kind",

"accounts"."CreatorId" AS "creator_id",

"creator"."Address" AS "creator",

"accounts"."PublicKey" AS "public_key"

FROM "public"."Accounts" AS "accounts"

LEFT JOIN "public"."Accounts" AS "delegate" ON "delegate"."Id" = "accounts"."DelegateId"

LEFT JOIN "public"."Accounts" AS "creator" ON "delegate"."Id" = "accounts"."CreatorId"

WHERE "accounts"."Type" = 2;

-- add view comments

COMMENT ON view "api"."contracts" IS 'Contract

Originated accounts (KT) that have code';

COMMENT ON column "api"."contracts"."address" IS 'Public key hash of the account';

COMMENT ON column "api"."contracts"."first_level" IS 'Block height of the first operation related to the account';

COMMENT ON column "api"."contracts"."last_level" IS 'Height of the block in which the account state was changed last time';

COMMENT ON column "api"."contracts"."balance" IS 'Account balance/Total balance of the delegate (baker), including spendable and frozen funds (micro tez)';

COMMENT ON column "api"."contracts"."counter" IS 'An account nonce which is used to prevent operation replay';

COMMENT ON column "api"."contracts"."num_contracts" IS 'Number of contracts, created (originated) and/or managed by the account';

COMMENT ON column "api"."contracts"."num_delegations" IS 'Number of delegation operations, related to the account';

COMMENT ON column "api"."contracts"."num_originations" IS 'Number of all origination (deployment / contract creation) operations, related to the account';

COMMENT ON column "api"."contracts"."num_transactions" IS 'Number of all transaction (tez transfer) operations, related to the account';

COMMENT ON column "api"."contracts"."num_reveals" IS 'Number of reveal (is used to reveal the public key associated with an account) operations of the contract';

COMMENT ON column "api"."contracts"."num_migrations" IS 'Number of migration (result of the context (database) migration during a protocol update) operations, related to the account (synthetic type)';

COMMENT ON column "api"."contracts"."delegate" IS 'Address of the current delegate of the account. null if it''s not delegated';

COMMENT ON column "api"."contracts"."delegation_level" IS 'Block height of latest delegation. null if it''s not delegated';

COMMENT ON column "api"."contracts"."is_staked" IS 'Whether account participates in staking (bakes or delegates)';

COMMENT ON column "api"."contracts"."kind" IS 'Sub kinds: delegator_contract (aka manager.tz) or smart_contract';

COMMENT ON column "api"."contracts"."creator" IS 'Address of the account, which has deployed the contract to the blockchain';

COMMENT ON column "api"."contracts"."public_key" IS 'Base58 representation of account''s public key, revealed by the account';

-- set view owner

ALTER VIEW "api"."contracts" OWNER TO "api_views_owner";

-- test

-- SELECT * FROM "api"."contracts";

-- -----------------------

-- activations

-- -----------------------

-- -----------------------

-- activations

-- -----------------------

-- create view

CREATE OR REPLACE VIEW "api"."activations" AS

SELECT

"activations"."Level" AS "level",

"activations"."Timestamp" AS "timestamp",

"activations"."OpHash" AS "hash",

"activations"."AccountId" AS "account_id",

"account"."Address" AS "account",

"activations"."Balance" AS "balance"

FROM

"public"."ActivationOps" AS "activations"

LEFT JOIN "public"."Accounts" AS "account" ON "account"."Id" = "activations"."AccountId";

-- add view comments

COMMENT ON view "api"."activations" IS 'Activation

Used to activate accounts that were recommended allocations of tezos tokens for donations to the Tezos Foundation’s fundraiser';

COMMENT ON column "api"."activations"."level" IS 'The height of the block, from the genesis block, in which the operation was included';

COMMENT ON column "api"."activations"."timestamp" IS 'Datetime of the block, in which the operation was included (ISO 8601, e.g. 2020-02-20T02:40:57Z)';

COMMENT ON column "api"."activations"."hash" IS 'Hash of the operation';

COMMENT ON column "api"."activations"."account" IS 'Address of the activated account';

COMMENT ON column "api"."activations"."balance" IS 'Account activation balance of tezos tokens that were recommended allocations for donations to the Tezos Foundation’s fundraiser';

-- set view owner

ALTER VIEW "api"."activations" OWNER TO "api_views_owner";

-- test

-- SELECT * FROM "api"."activations";

-- -----------------------

-- summary

-- -----------------------

-- create view

CREATE OR REPLACE VIEW "api"."summary" AS

SELECT

"summary"."Level" AS "level",

"summary"."Timestamp" AS "timestamp",

"summary"."Protocol" AS "protocol",

"summary"."NextProtocol" AS "next_protocol",

"summary"."Hash" AS "block_hash",

"summary"."AccountsCount" AS "num_accounts",

"summary"."ProtocolsCount" AS "num_protocols",

"summary"."ActivationOpsCount" AS "num_activations",

"summary"."BallotOpsCount" AS "num_ballots",

"summary"."DelegationOpsCount" AS "num_delegations",

"summary"."DoubleBakingOpsCount" AS "num_double_bakings",

"summary"."DoubleEndorsingOpsCount" AS "num_double_endorsings",

"summary"."EndorsementOpsCount" AS "num_endorsements",

"summary"."NonceRevelationOpsCount" AS "num_nonce_revelations",

"summary"."OriginationOpsCount" AS "num_originations",

"summary"."ProposalOpsCount" AS "num_proposals",

"summary"."RevealOpsCount" AS "num_reveals",

"summary"."TransactionOpsCount" AS "num_transactions",

"summary"."MigrationOpsCount" AS "num_migrations",

"summary"."RevelationPenaltyOpsCount" AS "num_revelation_penalties",

"summary"."ProposalsCount" AS "num_protocol_proposals",

"summary"."CyclesCount" AS "num_cycles",

"summary"."QuoteBtc" AS "xtz_btc",

"summary"."QuoteEur" AS "xtz_eur",

"summary"."QuoteLevel" AS "quote_level",

"summary"."QuoteUsd" AS "xtz_usd"

FROM

"public"."AppState" AS "summary";

-- add view comments

COMMENT ON view "api"."summary" IS 'Summary

';

COMMENT ON column "api"."summary"."level" IS 'The height of the block';

COMMENT ON column "api"."summary"."timestamp" IS 'Datetime of the block (ISO 8601, e.g. 2020-02-20T02:40:57Z)';

COMMENT ON column "api"."summary"."protocol" IS 'Protocol hash';

COMMENT ON column "api"."summary"."next_protocol" IS 'Next protocol hash';

COMMENT ON column "api"."summary"."block_hash" IS 'Block hash';

COMMENT ON column "api"."summary"."num_accounts" IS 'Total number of accounts of all types (user, delegate, contract) seen till this block (inclusive)';

COMMENT ON column "api"."summary"."num_activations" IS 'Total number of account activations applied till this block (inclusive)';

COMMENT ON column "api"."summary"."num_ballots" IS 'Total number of ballot operations applied till this block (inclusive)';

COMMENT ON column "api"."summary"."num_delegations" IS 'Total number of delegation operations applied till this block (inclusive)';

COMMENT ON column "api"."summary"."num_double_bakings" IS 'Total number of double baking evidences published till this block (inclusive)';

COMMENT ON column "api"."summary"."num_double_endorsings" IS 'Total number of double endorsing evidences published till this block (inclusive)';

COMMENT ON column "api"."summary"."num_endorsements" IS 'Total number of endorsements applied till this block (inclusive)';

COMMENT ON column "api"."summary"."num_nonce_revelations" IS 'Total number of nonce revelations applied till this block (inclusive)';

COMMENT ON column "api"."summary"."num_originations" IS 'Total number of originations applied till this block (inclusive)';

COMMENT ON column "api"."summary"."num_proposals" IS 'Total number of proposal operations applied till this block (inclusive)';

COMMENT ON column "api"."summary"."num_reveals" IS 'Total number of reveal operations applied till this block (inclusive)';

COMMENT ON column "api"."summary"."num_transactions" IS 'Total number of transactions applied till this block (inclusive)';

COMMENT ON column "api"."summary"."num_migrations" IS 'Total number of migrations applied till this block (inclusive)';

COMMENT ON column "api"."summary"."num_revelation_penalties" IS 'Total number of revelation penalties applied till this block (inclusive)';

COMMENT ON column "api"."summary"."num_protocol_proposals" IS 'Total number of protocol proposals seen till this block (inclusive)';

COMMENT ON column "api"."summary"."num_cycles" IS 'Total number of cycles';

COMMENT ON column "api"."summary"."xtz_btc" IS 'XTZ/BTC price';

COMMENT ON column "api"."summary"."xtz_eur" IS 'XTZ/EUR price';

COMMENT ON column "api"."summary"."quote_level" IS 'Used in async mode';

COMMENT ON column "api"."summary"."xtz_usd" IS 'XTZ/USD price';

-- set view owner

ALTER VIEW "api"."summary" OWNER TO "api_views_owner";

-- test

SELECT * FROM "api"."summary";

-- -----------------------

-- rewards

-- -----------------------

-- create view

CREATE OR REPLACE VIEW "api"."rewards" AS

SELECT

"rewards"."Cycle" AS "cycle",

"rewards"."BakerId" AS "baker_id",

"baker"."Address" AS "baker",

"rewards"."Rolls" AS "num_rolls",

"rewards"."StakingBalance" AS "staking_balance",

"rewards"."DelegatedBalance" AS "delegated_balance",

"rewards"."DelegatorsCount" AS "num_delegators",

"rewards"."FutureBlocks" AS "num_future_blocks",

"rewards"."OwnBlocks" AS "num_own_blocks",

"rewards"."ExtraBlocks" AS "num_extra_blocks",

"rewards"."MissedOwnBlocks" AS "num_missed_own_blocks",

"rewards"."MissedExtraBlocks" AS "num_missed_extra_blocks",

"rewards"."UncoveredOwnBlocks" AS "num_uncovered_own_blocks",

"rewards"."UncoveredExtraBlocks" AS "num_uncovered_extra_blocks",

"rewards"."FutureEndorsements" AS "num_future_endorsements",

"rewards"."Endorsements" AS "num_endorsements",

"rewards"."MissedEndorsements" AS "num_missed_endorsements",

"rewards"."UncoveredEndorsements" AS "num_uncovered_endorsements",

"rewards"."FutureBlockRewards" AS "future_block_rewards",

"rewards"."OwnBlockRewards" AS "own_block_rewards",

"rewards"."ExtraBlockRewards" AS "extra_block_rewards",

"rewards"."MissedOwnBlockRewards" AS "missed_own_block_rewards",

"rewards"."MissedExtraBlockRewards" AS "missed_extra_block_rewards",

"rewards"."UncoveredOwnBlockRewards" AS "uncovered_own_block_rewards",

"rewards"."UncoveredExtraBlockRewards" AS "uncovered_extra_block_rewards",

"rewards"."FutureEndorsementRewards" AS "future_endorsement_rewards",

"rewards"."EndorsementRewards" AS "endorsement_rewards",

"rewards"."MissedEndorsementRewards" AS "missed_endorsement_rewards",

"rewards"."UncoveredEndorsementRewards" AS "uncovered_endorsement_rewards",

"rewards"."OwnBlockFees" AS "own_block_fees",

"rewards"."ExtraBlockFees" AS "extra_block_fees",

"rewards"."MissedOwnBlockFees" AS "missed_own_block_fees",

"rewards"."MissedExtraBlockFees" AS "missed_extra_block_fees",

"rewards"."UncoveredOwnBlockFees" AS "uncovered_own_block_fees",

"rewards"."UncoveredExtraBlockFees" AS "uncovered_extra_block_fees",

"rewards"."DoubleBakingRewards" AS "double_baking_rewards",

"rewards"."DoubleBakingLostDeposits" AS "double_baking_lost_deposits",

"rewards"."DoubleBakingLostRewards" AS "double_baking_lost_rewards",

"rewards"."DoubleBakingLostFees" AS "double_baking_lost_fees",

"rewards"."DoubleEndorsingRewards" AS "double_endorsing_rewards",

"rewards"."DoubleEndorsingLostDeposits" AS "double_endorsing_lost_deposits",

"rewards"."DoubleEndorsingLostRewards" AS "double_endorsing_lost_rewards",

"rewards"."DoubleEndorsingLostFees" AS "double_endorsing_lost_fees",

"rewards"."RevelationRewards" AS "revelation_rewards",

"rewards"."RevelationLostRewards" AS "revelation_lost_rewards",

"rewards"."RevelationLostFees" AS "revelation_lost_fees",

"rewards"."FutureBlockDeposits" AS "future_block_deposits",

"rewards"."BlockDeposits" AS "block_deposits",

"rewards"."FutureEndorsementDeposits" AS "future_endorsement_deposits",

"rewards"."EndorsementDeposits" AS "endorsement_deposits",

"rewards"."ExpectedBlocks" AS "expected_blocks",

"rewards"."ExpectedEndorsements" AS "expected_endorsements",

COALESCE("rewards"."OwnBlockRewards", 0) COALESCE("rewards"."ExtraBlockRewards", 0) COALESCE("rewards"."EndorsementRewards", 0) COALESCE("rewards"."OwnBlockFees", 0) COALESCE("rewards"."ExtraBlockFees", 0) COALESCE("rewards"."RevelationRewards", 0) COALESCE("rewards"."DoubleBakingRewards", 0) COALESCE("rewards"."DoubleEndorsingRewards") AS "total_rewards",

COALESCE("rewards"."DoubleBakingLostDeposits", 0) COALESCE("rewards"."DoubleBakingLostRewards", 0) COALESCE("rewards"."DoubleBakingLostFees", 0) COALESCE("rewards"."DoubleEndorsingLostDeposits", 0) COALESCE("rewards"."DoubleEndorsingLostRewards", 0) COALESCE("rewards"."DoubleEndorsingLostFees", 0) COALESCE("rewards"."RevelationLostRewards", 0) COALESCE("rewards"."RevelationLostFees") AS "total_penalties"

FROM

"public"."BakerCycles" AS "rewards"

LEFT JOIN "public"."Accounts" AS "baker" ON "baker"."Id" = "rewards"."BakerId";

-- add view comments

COMMENT ON view "api"."rewards" IS 'Reward

Baker rewards/penalties per cycle with breakdown by reward/penalty kind';

COMMENT ON column "api"."rewards"."cycle" IS 'Cycle in which rewards have been or will be earned.';

COMMENT ON column "api"."rewards"."baker" IS 'Baker address';

COMMENT ON column "api"."rewards"."staking_balance" IS 'Staking balance of the baker at the snapshot time.';

COMMENT ON column "api"."rewards"."delegated_balance" IS 'Balance delegated to the baker at the snapshot time (sum of delegators'' balances).';

COMMENT ON column "api"."rewards"."num_delegators" IS 'Number of delegators at the snapshot time.';

COMMENT ON column "api"."rewards"."num_future_blocks" IS 'Number of blocks which baker is allowed to produce in this cycle based on future baking rights.';

COMMENT ON column "api"."rewards"."num_own_blocks" IS 'Number of successfully baked blocks with priority 0.';

COMMENT ON column "api"."rewards"."num_extra_blocks" IS 'Number of successfully baked blocks with priority 1 .';

COMMENT ON column "api"."rewards"."num_missed_own_blocks" IS 'Number of blocks which were missed at priority 0 for no apparent reason (usually due to issues with network or node).';

COMMENT ON column "api"."rewards"."num_missed_extra_blocks" IS 'Number of blocks which were missed at priority 1 for no apparent reason (usually due to issues with network or node).';

COMMENT ON column "api"."rewards"."num_uncovered_own_blocks" IS 'Number of blocks which were missed at priority 0 due to lack of bonds (for example, when a baker is overdelegated).';

COMMENT ON column "api"."rewards"."num_uncovered_extra_blocks" IS 'Number of blocks which were missed at priority 1 due to lack of bonds (for example, when a baker is overdelegated).';

COMMENT ON column "api"."rewards"."num_future_endorsements" IS 'Number of slots which baker is allowed to validate in this cycle based on future endorsing rights.';

COMMENT ON column "api"."rewards"."num_endorsements" IS 'Number of successfully endorsed slots.';

COMMENT ON column "api"."rewards"."num_missed_endorsements" IS 'Number of endorsing slots which were missed for no apparent reason (usually due to issues with network or node).';

COMMENT ON column "api"."rewards"."num_uncovered_endorsements" IS 'Number of endorsing slots which were missed due to lack of bonds (for example, when a baker is overdelegated).';

COMMENT ON column "api"."rewards"."future_block_rewards" IS 'Estimated value of future block rewards.';

COMMENT ON column "api"."rewards"."own_block_rewards" IS 'Rewards received for blocks baked with priority 0.';

COMMENT ON column "api"."rewards"."extra_block_rewards" IS 'Rewards received for blocks baked with priority 1 .';

COMMENT ON column "api"."rewards"."missed_own_block_rewards" IS 'Rewards which were not received due to missing own blocks.';

COMMENT ON column "api"."rewards"."missed_extra_block_rewards" IS 'Rewards which were not received due to missing extra blocks.';

COMMENT ON column "api"."rewards"."uncovered_own_block_rewards" IS 'Rewards which were not received due to missing own blocks due to lack of bonds.';

COMMENT ON column "api"."rewards"."uncovered_extra_block_rewards" IS 'Rewards which were not received due to missing extra blocks due to lack of bonds.';

COMMENT ON column "api"."rewards"."future_endorsement_rewards" IS 'Estimated value of future endorsing rewards.';

COMMENT ON column "api"."rewards"."endorsement_rewards" IS 'Rewards received for endorsed slots.';

COMMENT ON column "api"."rewards"."missed_endorsement_rewards" IS 'Rewards which were not received due to missing endorsements.';

COMMENT ON column "api"."rewards"."uncovered_endorsement_rewards" IS 'Rewards which were not received due to missing endorsements due to lack of bonds.';

COMMENT ON column "api"."rewards"."own_block_fees" IS 'Operation fees which were harvested from successfully baked blocks with priority 0.';

COMMENT ON column "api"."rewards"."extra_block_fees" IS 'Operation fees which were harvested from successfully baked blocks with priority 1 .';

COMMENT ON column "api"."rewards"."missed_own_block_fees" IS 'Operation fees which were not received due to missing own blocks.';

COMMENT ON column "api"."rewards"."missed_extra_block_fees" IS 'Operation fees which were not received due to missing extra blocks.';

COMMENT ON column "api"."rewards"."uncovered_own_block_fees" IS 'Operation fees which were not received due to missing own blocks (due to lack of bonds).';

COMMENT ON column "api"."rewards"."uncovered_extra_block_fees" IS 'Operation fees which were not received due to missing extra blocks (due to lack of bonds).';

COMMENT ON column "api"."rewards"."double_baking_rewards" IS 'Rewards for detecting double baking (accusing someone of producing two different blocks at the same level).';

COMMENT ON column "api"."rewards"."double_baking_lost_deposits" IS 'Bonds lost due to double baking';

COMMENT ON column "api"."rewards"."double_baking_lost_rewards" IS 'Rewards lost due to double baking';

COMMENT ON column "api"."rewards"."double_baking_lost_fees" IS 'Fees lost due to double baking';

COMMENT ON column "api"."rewards"."double_endorsing_rewards" IS 'Rewards for detecting double endorsing (accusing someone of validating two different blocks at the same level).';

COMMENT ON column "api"."rewards"."double_endorsing_lost_deposits" IS 'Bonds lost due to double endorsing';

COMMENT ON column "api"."rewards"."double_endorsing_lost_rewards" IS 'Rewards lost due to double endorsing';

COMMENT ON column "api"."rewards"."double_endorsing_lost_fees" IS 'Fees lost due to double endorsing';

COMMENT ON column "api"."rewards"."revelation_rewards" IS 'Rewards for including into a block seed nonce revelation operations.';

COMMENT ON column "api"."rewards"."revelation_lost_rewards" IS 'Rewards lost due to missing seed nonce revelation.';

COMMENT ON column "api"."rewards"."revelation_lost_fees" IS 'Fees lost due to missing seed nonce revelation.';

COMMENT ON column "api"."rewards"."future_block_deposits" IS 'Estimated value of bonds which will be locked as security deposit for future blocks.';

COMMENT ON column "api"."rewards"."block_deposits" IS 'Bonds which were locked as a security deposit for baking own and extra blocks.';

COMMENT ON column "api"."rewards"."future_endorsement_deposits" IS 'Estimated value of bonds which will be locked as security deposit for future endorsements.';

COMMENT ON column "api"."rewards"."endorsement_deposits" IS 'Bonds which were locked as a security deposit for endorsed slots.';

COMMENT ON column "api"."rewards"."expected_blocks" IS 'Expected value of how many blocks baker should produce based on baker''s rolls, total rolls and blocks per cycle.';

COMMENT ON column "api"."rewards"."expected_endorsements" IS 'Expected value of how many slots baker should validate based on baker''s rolls, total rolls and endorsing slots per cycle.';

COMMENT ON column "api"."rewards"."total_rewards" IS 'Total cycle rewards (positive)';

COMMENT ON column "api"."rewards"."total_penalties" IS 'Total cycle penalties';

-- set view owner

ALTER VIEW "api"."rewards" OWNER TO "api_views_owner";

-- test

SELECT * FROM "api"."rewards";

-- -----------------------

-- rights

-- -----------------------

-- create view

CREATE OR REPLACE VIEW "api"."rights" AS

SELECT

"rights"."Cycle" AS "cycle",

"rights"."Level" AS "level",

"rights"."BakerId" AS "baker_id",

"baker"."Address" AS "baker",

CASE

WHEN "rights"."Type" = 0 THEN 'baking'

WHEN "rights"."Type" = 1 THEN 'endorsing'

ELSE NULL

END AS "type",

CASE

WHEN "rights"."Status" = 0 THEN 'future'

WHEN "rights"."Status" = 1 THEN 'realized'

WHEN "rights"."Status" = 2 THEN 'uncovered'

WHEN "rights"."Status" = 3 THEN 'missed'

ELSE NULL

END AS "status",

"rights"."Priority" AS "priority",

"rights"."Slots" AS "num_slots"

FROM

"public"."BakingRights" AS "rights"

LEFT JOIN "public"."Accounts" AS "baker" ON "baker"."Id" = "rights"."BakerId";

-- add view comments

COMMENT ON view "api"."rights" IS 'Right

';

COMMENT ON column "api"."rights"."cycle" IS 'Cycle on which the right can be realized.';

COMMENT ON column "api"."rights"."level" IS 'Level at which a block must be baked or an endorsement must be sent.';

COMMENT ON column "api"."rights"."baker" IS 'Baker address to which baking or endorsing right has been given.';

COMMENT ON column "api"."rights"."type" IS 'baking - right to bake (produce) a block;\nendorsing - right to endorse (validate) a block.';

COMMENT ON column "api"."rights"."status" IS 'future - the right is not realized yet;\nrealized - the right was successfully realized;\nuncovered - the right was not realized due to lack of bonds (for example, when a baker is overdelegated);\nmissed - the right was not realized for no apparent reason (usually due to issues with network or node).';

COMMENT ON column "api"."rights"."priority" IS 'Priority (0 - ∞) with which baker can produce a block. If a baker with priority 0 doesn''t produce a block within a given time interval, then the right goes to a baker with priority1, etc. For endorsing rights this field is always null.';

COMMENT ON column "api"."rights"."num_slots" IS 'Number of slots (1 - 32) to be endorsed. For baking rights this field is always null.';

-- set view owner

ALTER VIEW "api"."rights" OWNER TO "api_views_owner";

-- test

-- SELECT * FROM "api"."rights";

-- -----------------------

-- ballots

-- -----------------------

-- create view

/* CREATE OR REPLACE VIEW "api"."ballots" AS

SELECT

"ballots"."Level" AS "level",

"ballots"."Timestamp" AS "timestamp",

"ballots"."OpHash" AS "hash",

"ballots"."PeriodId" AS "period_id",

-- "voting_period"."Index" AS "voting_period",

"ballots"."ProposalId" AS "proposal_id",

"proposal"."Hash" AS "proposal",

"ballots"."SenderId" AS "baker_id",

"baker"."Address" AS "baker",

"ballots"."Rolls" AS "num_rolls",

CASE

WHEN "ballots"."Vote" = 0 THEN 'yay'

WHEN "ballots"."Vote" = 1 THEN 'nay'

WHEN "ballots"."Vote" = 2 THEN 'pass'

ELSE NULL

END AS "vote"

FROM

"public"."BallotOps" AS "ballots"

-- LEFT JOIN "public"."VotingPeriods" AS "voting_period" ON "voting_period"."Id" = "ballots"."PeriodId"

LEFT JOIN "public"."Proposals" AS "proposal" ON "proposal"."Id" = "ballots"."ProposalId"

LEFT JOIN "public"."Accounts" AS "baker" ON "baker"."Id" = "ballots"."SenderId";

-- add view comments

COMMENT ON view "api"."ballots" IS 'Ballot

Used to vote for a proposal in a given voting cycle';

COMMENT ON column "api"."ballots"."level" IS 'The height of the block from the genesis block, in which the operation was included';

COMMENT ON column "api"."ballots"."timestamp" IS 'Datetime of the block, in which the operation was included (ISO 8601, e.g. 2020-02-20T02:40:57Z)';

COMMENT ON column "api"."ballots"."hash" IS 'Hash of the operation';

-- COMMENT ON column "api"."ballots"."voting_period" IS 'Index of the voting period for which the ballot was submitted';

COMMENT ON column "api"."ballots"."proposal" IS 'Hash of the proposal for which ballot was submitted';

COMMENT ON column "api"."ballots"."baker" IS 'Information about the delegate (baker), submitted the ballot';

COMMENT ON column "api"."ballots"."num_rolls" IS 'Number of baker''s rolls (baker''s voting power)';

COMMENT ON column "api"."ballots"."vote" IS 'Vote, given in the ballot (yay, nay, or pass)';

-- set view owner

ALTER VIEW "api"."ballots" OWNER TO "api_views_owner"; */

-- test

-- SELECT * FROM "api"."ballots";

-- -----------------------

-- blocks

-- -----------------------

-- create view

CREATE OR REPLACE VIEW "api"."blocks" AS

SELECT

"blocks"."Level" AS "level",

"blocks"."Hash" AS "hash",

"blocks"."Timestamp" AS "timestamp",

"blocks"."ProtoCode" AS "proto_code",

"blocks"."Priority" AS "priority",

"blocks"."Validations" AS "num_endorsements",

("blocks"."Events" & 1 > 0) AS "is_cycle_start",

("blocks"."Events" & 2 > 0) AS "is_cycle_end",

("blocks"."Events" & 4 > 0) AS "is_protocol_start",

("blocks"."Events" & 8 > 0) AS "is_protocol_end",

("blocks"."Events" & 16 > 0) AS "is_voting_period_start",

("blocks"."Events" & 32 > 0) AS "is_voring_period_end",

("blocks"."Events" & 64 > 0) AS "has_deactivations",

("blocks"."Events" & 128 > 0) AS "has_account_allocations",

("blocks"."Events" & 256 > 0) AS "has_smart_contract_interaction",

("blocks"."Events" & 512 > 0) AS "is_snapshot",

("blocks"."Events" & 1024 > 0) AS "has_delegator_contract_interaction",

("blocks"."Operations" & 2 > 0) AS "has_ballots",

("blocks"."Operations" & 4 > 0) AS "has_proposals",

("blocks"."Operations" & 8 > 0) AS "has_activations",

("blocks"."Operations" & 16 > 0) AS "has_double_bakings",

("blocks"."Operations" & 32 > 0) AS "has_double_endorsings",

("blocks"."Operations" & 64 > 0) AS "has_revelations",

("blocks"."Operations" & 128 > 0) AS "has_transactions",

("blocks"."Operations" & 256 > 0) AS "has_reveals",

("blocks"."Operations" & 512 > 0) AS "has_migrations",

("blocks"."Operations" & 1024 > 0) AS "has_revelations_penalties",

"blocks"."Reward" AS "reward",

"blocks"."Fees" AS "fees",

"blocks"."BakerId" AS "baker_id",

"baker"."Address" AS "baker",

"blocks"."RevelationId" AS "revelation_id"

FROM

"public"."Blocks" AS "blocks"

LEFT JOIN "public"."Accounts" AS "baker" ON "baker"."Id" = "blocks"."BakerId";

-- add view comments

COMMENT ON view "api"."blocks" IS 'Block

';

COMMENT ON column "api"."blocks"."level" IS 'The height of the block from the genesis block';

COMMENT ON column "api"."blocks"."hash" IS 'Block hash';

COMMENT ON column "api"."blocks"."timestamp" IS 'The datetime at which the block is claimed to have been created (ISO 8601, e.g. 2020-02-20T02:40:57Z)';

COMMENT ON column "api"."blocks"."proto_code" IS 'Protocol code, representing a number of protocol changes since genesis (mod 256, but -1 for the genesis block)';

COMMENT ON column "api"."blocks"."priority" IS 'The position in the priority list of delegates at which the block was baked';

COMMENT ON column "api"."blocks"."num_endorsements" IS 'Number of endorsements, confirming the block';

COMMENT ON column "api"."blocks"."is_cycle_start" IS 'This is the first block of the cycle';

COMMENT ON column "api"."blocks"."is_cycle_end" IS 'This is the last block of the cycle';

COMMENT ON column "api"."blocks"."is_protocol_start" IS 'This is the first block of a protocol';

COMMENT ON column "api"."blocks"."is_protocol_end" IS 'This is the last block of a protocol';

COMMENT ON column "api"."blocks"."is_voting_period_start" IS 'This is the first block of a voting period';

COMMENT ON column "api"."blocks"."is_voring_period_end" IS 'This is the last block of a voting period';

COMMENT ON column "api"."blocks"."has_deactivations" IS 'At least one delegate (baker) was deactivated in this block';

COMMENT ON column "api"."blocks"."has_account_allocations" IS 'At least one account was allocated (via origination, activation, or transaction to a non-existent address) was deactivated in this block';

COMMENT ON column "api"."blocks"."has_smart_contract_interaction" IS 'In this block a smart-contract (excluding manager.tz) was deployed or invoked.';

COMMENT ON column "api"."blocks"."is_snapshot" IS 'This block was randomly chosen for making a snapshot of balances.';

COMMENT ON column "api"."blocks"."has_delegator_contract_interaction" IS 'In this block a delegator contract (manager.tz) was deployed or invoked.';

COMMENT ON column "api"."blocks"."has_ballots" IS 'At least one ballot operation was injected in this block.';

COMMENT ON column "api"."blocks"."has_proposals" IS 'At least one proposal operation was injected in this block.';

COMMENT ON column "api"."blocks"."has_activations" IS 'At least one activation operation was injected in this block.';

COMMENT ON column "api"."blocks"."has_double_bakings" IS 'At least one double baking accusation was injected in this block.';

COMMENT ON column "api"."blocks"."has_double_endorsings" IS 'At least one double endorsement accusation was in during this block.';

COMMENT ON column "api"."blocks"."has_revelations" IS 'At least one nonce revelation operation was injected in this block.';

COMMENT ON column "api"."blocks"."has_transactions" IS 'At least one transaction operation was injected in this block.';

COMMENT ON column "api"."blocks"."has_reveals" IS 'At least one reveal operation was injected in this block.';

COMMENT ON column "api"."blocks"."has_migrations" IS 'A context migration happened in this block.';

COMMENT ON column "api"."blocks"."has_revelations_penalties" IS 'A penalty for not revealing seed nonce was applied in this block.';

COMMENT ON column "api"."blocks"."reward" IS 'Reward of the baker for producing the block (micro tez)';

COMMENT ON column "api"."blocks"."fees" IS 'Total fee paid by all operations, included in the block';

COMMENT ON column "api"."blocks"."baker_id" IS 'Accounts';

COMMENT ON column "api"."blocks"."baker" IS 'Address of the delegate (baker), produced the block';

-- set view owner

ALTER VIEW "api"."blocks" OWNER TO "api_views_owner";

-- test

-- SELECT * FROM "api"."blocks";

-- -----------------------

-- cycles

-- -----------------------

-- create view

CREATE OR REPLACE VIEW "api"."cycles" AS

SELECT

"cycles"."Index" AS "index",

"cycles"."SnapshotIndex" AS "snapshot_index",

"cycles"."SnapshotLevel" AS "snapshot_level",

"cycles"."TotalRolls" AS "total_rolls",

"cycles"."TotalStaking" AS "total_staking",

"cycles"."TotalDelegated" AS "total_delegated",

"cycles"."TotalDelegators" AS "total_delegators",

"cycles"."TotalBakers" AS "total_bakers",

"cycles"."Seed" AS "seed"

FROM

"public"."Cycles" AS "cycles";

-- add view comments

COMMENT ON view "api"."cycles" IS 'Cycle

';

COMMENT ON column "api"."cycles"."index" is 'Cycle index starting from zero';

COMMENT ON column "api"."cycles"."snapshot_index" is 'Relative index of the snapshot';

COMMENT ON column "api"."cycles"."snapshot_level" is 'Height of the block where the snapshot was taken';

COMMENT ON column "api"."cycles"."total_rolls" is 'Total number of rolls involved in baking rights distribution';

COMMENT ON column "api"."cycles"."total_staking" is 'Total staking balance of all active in this cycle bakers';

COMMENT ON column "api"."cycles"."total_delegated" is 'Total balance delegated to active bakers';

COMMENT ON column "api"."cycles"."total_delegators" is 'Total number of active bakers'' delegators';

COMMENT ON column "api"."cycles"."total_bakers" is 'Total number of all active in this cycle bakers';

COMMENT ON column "api"."cycles"."seed" is 'Randomly generated seed used by the network for things like baking rights distribution etc.';

-- set view owner

ALTER VIEW "api"."cycles" OWNER TO "api_views_owner";

-- test

-- SELECT * FROM "api"."cycles";

-- -----------------------

-- delegations

-- -----------------------

-- create view

CREATE OR REPLACE VIEW "api"."delegations" AS

SELECT

"delegations"."Level" AS "level",

"delegations"."Timestamp" AS "timestamp",

"delegations"."OpHash" AS "hash",

"delegations"."SenderId" AS "sender_id",

"sender"."Address" AS "sender",

"delegations"."Counter" AS "counter",

"delegations"."BakerFee" AS "baker_fee",

"delegations"."GasLimit" AS "gas_limit",

"delegations"."GasUsed" AS "consumed_gas",

CASE

WHEN "delegations"."Status" = 1 THEN 'applied'

WHEN "delegations"."Status" = 2 THEN 'backtracked'

WHEN "delegations"."Status" = 3 THEN 'skipped'

WHEN "delegations"."Status" = 4 THEN 'failed'

ELSE NULL

END AS "status",

"delegations"."Errors" AS "errors",

"delegations"."InitiatorId" AS "initiator_id",

"initiator"."Address" AS "initiator",

"delegations"."Nonce" AS "nonce",

"delegations"."DelegateId" AS "delegate_id",

"delegate"."Address" AS "delegate",

"delegations"."PrevDelegateId" AS "prev_delegate_id",

"prev_delegate"."Address" AS "prev_delegate"

FROM

"public"."DelegationOps" AS "delegations"

LEFT JOIN "public"."Accounts" AS "sender" ON "sender"."Id" = "delegations"."SenderId"

LEFT JOIN "public"."Accounts" AS "initiator" ON "initiator"."Id" = "delegations"."InitiatorId"

LEFT JOIN "public"."Accounts" AS "delegate" ON "delegate"."Id" = "delegations"."DelegateId"

LEFT JOIN "public"."Accounts" AS "prev_delegate" ON "prev_delegate"."Id" = "delegations"."PrevDelegateId";

-- add view comments

COMMENT ON view "api"."delegations" IS 'Delegation

Used to delegate funds to a delegate (an implicit account registered as a baker)';

COMMENT ON column "api"."delegations"."level" is 'The height of the block from the genesis block, in which the operation was included';

COMMENT ON column "api"."delegations"."timestamp" is 'Datetime of the block, in which the operation was included (ISO 8601, e.g. 2020-02-20T02:40:57Z)';

COMMENT ON column "api"."delegations"."hash" is 'Hash of the operation';

COMMENT ON column "api"."delegations"."sender" is 'Address of the delegated account';

COMMENT ON column "api"."delegations"."counter" is 'An account nonce which is used to prevent operation replay';

COMMENT ON column "api"."delegations"."baker_fee" is 'Fee to a baker, produced block, in which the operation was included';

COMMENT ON column "api"."delegations"."gas_limit" is 'A cap on the amount of gas a given operation can consume';

COMMENT ON column "api"."delegations"."consumed_gas" is 'Amount of gas, consumed by the operation';

COMMENT ON column "api"."delegations"."status" is 'Operation status (applied - an operation applied by the node and successfully added to the blockchain, failed - an operation which failed with some particular error (not enough balance, gas limit, etc), backtracked - an operation which was successful but reverted due to one of the following operations in the same operation group was failed, skipped - all operations after the failed one in an operation group)';

COMMENT ON column "api"."delegations"."errors" is 'List of errors provided by the node, which has injected the operation to the blockchain. null if there is no errors';

COMMENT ON column "api"."delegations"."initiator" is 'Address of the initiator of the delegation contract call';

COMMENT ON column "api"."delegations"."nonce" is 'An account nonce which is used to prevent internal operation replay';

COMMENT ON column "api"."delegations"."delegate" is 'Address of the delegate (baker) that accepted the delegation. null if delegate is not specified (undelegation operation)';

COMMENT ON column "api"."delegations"."prev_delegate" is 'Address of the previous delegate of the account. null if there is no previous delegate';

-- set view owner

ALTER VIEW "api"."delegations" OWNER TO "api_views_owner";

-- test

-- SELECT * FROM "api"."delegations";

-- -----------------------

-- balance_snapshots

-- -----------------------

-- create view

CREATE OR REPLACE VIEW "api"."balance_snapshots" AS

SELECT

"balance_snapshots"."Cycle" AS "cycle",

"balance_snapshots"."DelegatorId" AS "delegator_id",

"delegator"."Address" AS "delegator",

"balance_snapshots"."BakerId" AS "baker_id",

"baker"."Address" AS "baker",

"balance_snapshots"."Balance" AS "balance"

FROM

"public"."DelegatorCycles" AS "balance_snapshots"

LEFT JOIN "public"."Accounts" AS "delegator" ON "delegator"."Id" = "balance_snapshots"."DelegatorId"

LEFT JOIN "public"."Accounts" AS "baker" ON "baker"."Id" = "balance_snapshots"."BakerId";

-- add view comments

COMMENT ON view "api"."balance_snapshots" IS 'Balance Snapshot

';

-- set view owner

ALTER VIEW "api"."balance_snapshots" OWNER TO "api_views_owner";

-- test

-- SELECT * FROM "api"."balance_snapshots";

-- -----------------------

-- double_bakings

-- -----------------------

-- create view

CREATE OR REPLACE VIEW "api"."double_bakings" AS

SELECT

"double_bakings"."Level" AS "level",

"double_bakings"."Timestamp" AS "timestamp",

"double_bakings"."OpHash" AS "hash",

"double_bakings"."AccusedLevel" AS "accused_level",

"double_bakings"."AccuserId" AS "accuser_id",

"accuser"."Address" AS "accuser",

"double_bakings"."AccuserReward" AS "accuser_reward",

"double_bakings"."OffenderId" AS "offender_id",

"offender"."Address" AS "offender",

"double_bakings"."OffenderLostDeposit" AS "offender_lost_deposits",

"double_bakings"."OffenderLostReward" AS "offender_lost_rewards",

"double_bakings"."OffenderLostFee" AS "offender_lost_fees"

FROM

"public"."DoubleBakingOps" AS "double_bakings"

LEFT JOIN "public"."Accounts" AS "accuser" ON "accuser"."Id" = "double_bakings"."AccuserId"

LEFT JOIN "public"."Accounts" AS "offender" ON "offender"."Id" = "double_bakings"."OffenderId";

-- add view comments

COMMENT ON view "api"."double_bakings" IS 'Double Baking

';

COMMENT ON column "api"."double_bakings"."level" IS 'The height of the block from the genesis block, in which the operation was included';

COMMENT ON column "api"."double_bakings"."timestamp" IS 'Datetime of the block, in which the operation was included (ISO 8601, e.g. 2020-02-20T02:40:57Z)';

COMMENT ON column "api"."double_bakings"."hash" IS 'Hash of the operation';

COMMENT ON column "api"."double_bakings"."accused_level" IS 'The height of the block from the genesis block, which was double baked';

COMMENT ON column "api"."double_bakings"."accuser" IS 'Address of the baker (delegate), produced the block, in which the operation was included';

COMMENT ON column "api"."double_bakings"."accuser_reward" IS 'Reward of the baker (delegate), produced the block, in which the operation was included';

COMMENT ON column "api"."double_bakings"."offender" IS 'Address of the baker (delegate), accused for producing two different blocks at the same height';

COMMENT ON column "api"."double_bakings"."offender_lost_deposits" IS 'Amount of frozen security deposit, lost by accused baker (delegate)';

COMMENT ON column "api"."double_bakings"."offender_lost_rewards" IS 'Amount of frozen rewards, lost by accused baker (delegate)';

COMMENT ON column "api"."double_bakings"."offender_lost_fees" IS 'Amount of frozen fees, lost by accused baker (delegate)';

-- set view owner

ALTER VIEW "api"."double_bakings" OWNER TO "api_views_owner";

-- test

-- SELECT * FROM "api"."double_bakings";

-- -----------------------

-- double_endorsings

-- -----------------------

-- create view

CREATE OR REPLACE VIEW "api"."double_endorsings" AS

SELECT

"double_endorsings"."Level" AS "level",

"double_endorsings"."Timestamp" AS "timestamp",

"double_endorsings"."OpHash" AS "hash",

"double_endorsings"."AccusedLevel" AS "accused_level",

"double_endorsings"."AccuserId" AS "accuser_id",

"accuser"."Address" AS "accuser",

"double_endorsings"."AccuserReward" AS "accuser_reward",

"double_endorsings"."OffenderId" AS "offender_id",

"offender"."Address" AS "offender",

"double_endorsings"."OffenderLostDeposit" AS "offender_lost_deposits",

"double_endorsings"."OffenderLostReward" AS "offender_lost_rewards",

"double_endorsings"."OffenderLostFee" AS "offender_lost_fees"

FROM

"public"."DoubleEndorsingOps" AS "double_endorsings"

LEFT JOIN "public"."Accounts" AS "accuser" ON "accuser"."Id" = "double_endorsings"."AccuserId"

LEFT JOIN "public"."Accounts" AS "offender" ON "offender"."Id" = "double_endorsings"."OffenderId";

-- add view comments

COMMENT ON view "api"."double_endorsings" IS 'Double Endorsing

';

COMMENT ON column "api"."double_endorsings"."level" IS 'The height of the block from the genesis block, in which the operation was included';

COMMENT ON column "api"."double_endorsings"."timestamp" IS 'Datetime of the block, in which the operation was included (ISO 8601, e.g. 2020-02-20T02:40:57Z)';

COMMENT ON column "api"."double_endorsings"."hash" IS 'Hash of the operation';

COMMENT ON column "api"."double_endorsings"."accused_level" IS 'The height of the block from the genesis block, at which double endorsing occurred';

COMMENT ON column "api"."double_endorsings"."accuser" IS 'Address of the baker (delegate), produced the block, in which the operation was included';

COMMENT ON column "api"."double_endorsings"."accuser_reward" IS 'Reward of the baker (delegate), produced the block, in which the operation was included';

COMMENT ON column "api"."double_endorsings"."offender" IS 'Address of the baker (delegate), accused for producing two different endorsements at the same height';

COMMENT ON column "api"."double_endorsings"."offender_lost_deposits" IS 'Amount of frozen security deposit, lost by accused baker (delegate)';

COMMENT ON column "api"."double_endorsings"."offender_lost_rewards" IS 'Amount of frozen rewards, lost by accused baker (delegate)';

COMMENT ON column "api"."double_endorsings"."offender_lost_fees" IS 'Amount of frozen fees, lost by accused baker (delegate)';

-- set view owner

ALTER VIEW "api"."double_endorsings" OWNER TO "api_views_owner";

-- test

-- SELECT * FROM "api"."double_endorsings";

-- -----------------------

-- endorsements

-- -----------------------

-- create view

CREATE OR REPLACE VIEW "api"."endorsements" AS

SELECT

"endorsements"."Level" AS "level",

"endorsements"."Timestamp" AS "timestamp",

"endorsements"."OpHash" AS "hash",

"endorsements"."DelegateId" AS "delegate_id",

"delegate"."Address" AS "delegate",

"endorsements"."Slots" AS "num_slots",

"endorsements"."Reward" AS "reward"

FROM

"public"."EndorsementOps" AS "endorsements"

LEFT JOIN "public"."Accounts" AS "delegate" ON "delegate"."Id" = "endorsements"."DelegateId";

-- add view comments

COMMENT ON view "api"."endorsements" IS 'Endorsement

';

COMMENT ON column "api"."endorsements"."level" IS 'The height of the block from the genesis block, in which the operation was included';

COMMENT ON column "api"."endorsements"."timestamp" IS 'Datetime of the block, in which the operation was included (ISO 8601, e.g. 2020-02-20T02:40:57Z)';

COMMENT ON column "api"."endorsements"."hash" IS 'Hash of the operation';

COMMENT ON column "api"."endorsements"."delegate" IS 'Address of the baker (delegate) who sent the operation';

COMMENT ON column "api"."endorsements"."num_slots" IS 'Number of assigned endorsement slots (out of 32) to the baker (delegate) who sent the operation';

COMMENT ON column "api"."endorsements"."reward" IS 'Reward of the baker (delegate) for the operation';

-- set view owner

ALTER VIEW "api"."endorsements" OWNER TO "api_views_owner";

-- test

-- SELECT * FROM "api"."endorsements";