Skip to content
Snippets Groups Projects

Draft: 4.(balance_tracker) Addicional reward tracking, more optimalization of processing functions

Closed Draft: 4.(balance_tracker) Addicional reward tracking, more optimalization of processing functions
mzander/extended-rewards into develop
Closed Michal Zander requested to merge mzander/extended-rewards into develop
Compare
and
Show latest version
5 files
+ 68
− 67
Compare changes
  • Side-by-side
  • Inline
Files
5
db/btracker_app.sql
+ 35
− 22
DROP SCHEMA IF EXISTS btracker_app CASCADE;
CREATE SCHEMA IF NOT EXISTS btracker_app AUTHORIZATION btracker_owner;
GRANT USAGE ON SCHEMA btracker_app to btracker_user;
SET ROLE btracker_owner;
@@ -29,7 +28,7 @@ VALUES
CREATE TABLE IF NOT EXISTS btracker_app.current_account_balances
(
account VARCHAR NOT NULL, -- Balance owner account
account INT NOT NULL, -- Balance owner account
nai INT NOT NULL, -- Balance type (currency)
balance BIGINT NOT NULL, -- Balance value (amount of held tokens)
source_op BIGINT NOT NULL,-- The operation triggered last balance change
@@ -40,7 +39,7 @@ CREATE TABLE IF NOT EXISTS btracker_app.current_account_balances
CREATE TABLE IF NOT EXISTS btracker_app.current_account_rewards
(
account VARCHAR NOT NULL, -- Balance owner account
account INT NOT NULL, -- Balance owner account
nai INT NOT NULL, -- Balance type (currency)
balance BIGINT NOT NULL, -- Balance value (amount of held tokens)
source_op BIGINT NOT NULL,-- The operation triggered last balance change
@@ -51,8 +50,8 @@ CREATE TABLE IF NOT EXISTS btracker_app.current_account_rewards
CREATE TABLE IF NOT EXISTS btracker_app.current_accounts_delegations
(
delegator VARCHAR NOT NULL,
delegatee VARCHAR NOT NULL,
delegator INT NOT NULL,
delegatee INT NOT NULL,
balance BIGINT NOT NULL,
source_op BIGINT NOT NULL,
source_op_block INT NOT NULL,
@@ -62,17 +61,16 @@ CREATE TABLE IF NOT EXISTS btracker_app.current_accounts_delegations
CREATE TABLE IF NOT EXISTS btracker_app.current_account_vests
(
account VARCHAR NOT NULL,
account INT NOT NULL,
received_vests BIGINT DEFAULT 0,
delegated_vests BIGINT DEFAULT 0,
tmp BIGINT,
delegated_vests BIGINT DEFAULT 0,
CONSTRAINT pk_temp_vests PRIMARY KEY (account)
) INHERITS (hive.btracker_app);
CREATE TABLE IF NOT EXISTS btracker_app.current_account_withdraws
(
account VARCHAR NOT NULL,
account INT NOT NULL,
vesting_withdraw_rate BIGINT DEFAULT 0,
to_withdraw BIGINT DEFAULT 0,
withdrawn BIGINT DEFAULT 0,
@@ -81,18 +79,18 @@ CREATE TABLE IF NOT EXISTS btracker_app.current_account_withdraws
CONSTRAINT pk_current_account_withdraws PRIMARY KEY (account)
) INHERITS (hive.btracker_app);
CREATE TABLE IF NOT EXISTS btracker_app.current_account_withdraws_routes
CREATE TABLE IF NOT EXISTS btracker_app.current_account_routes
(
account VARCHAR NOT NULL,
to_account VARCHAR NOT NULL,
account INT NOT NULL,
to_account INT NOT NULL,
percent INT NOT NULL,
CONSTRAINT pk_current_account_withdraws_routes PRIMARY KEY (account, to_account)
CONSTRAINT pk_current_account_routes PRIMARY KEY (account, to_account)
) INHERITS (hive.btracker_app);
CREATE TABLE IF NOT EXISTS btracker_app.current_account_savings
(
account VARCHAR NOT NULL,
account INT NOT NULL,
nai INT NOT NULL,
saving_balance BIGINT DEFAULT 0,
source_op BIGINT NOT NULL,
@@ -104,7 +102,7 @@ CREATE TABLE IF NOT EXISTS btracker_app.current_account_savings
CREATE TABLE IF NOT EXISTS btracker_app.transfer_saving_id
(
account VARCHAR NOT NULL,
account INT NOT NULL,
nai INT NOT NULL,
balance BIGINT NOT NULL,
request_id BIGINT NOT NULL,
@@ -112,10 +110,18 @@ CREATE TABLE IF NOT EXISTS btracker_app.transfer_saving_id
CONSTRAINT pk_transfer_saving_id PRIMARY KEY (account, request_id)
) INHERITS (hive.btracker_app);
CREATE TABLE IF NOT EXISTS btracker_app.account_posting_curation_rewards
(
account INT NOT NULL,
posting_rewards BIGINT DEFAULT 0,
curation_rewards BIGINT DEFAULT 0,
CONSTRAINT pk_account_posting_curation_rewards PRIMARY KEY (account)
) INHERITS (hive.btracker_app);
CREATE TABLE IF NOT EXISTS btracker_app.account_balance_history
(
account VARCHAR NOT NULL, -- Balance owner account
account INT NOT NULL, -- Balance owner account
nai INT NOT NULL, -- Balance type (currency)
balance BIGINT NOT NULL, -- Balance value after a change
source_op BIGINT NOT NULL,-- The operation triggered given balance change
@@ -219,7 +225,7 @@ FOR __balance_change IN
FROM hive.operation_types ot
WHERE ot.name IN (SELECT * FROM hive.get_balance_impacting_operations())
)
SELECT bio.account_name AS account, bio.asset_symbol_nai AS nai, bio.amount as balance, ho.id AS source_op, ho.block_num AS source_op_block
SELECT av.id AS account, bio.asset_symbol_nai AS nai, bio.amount as balance, ho.id AS source_op, ho.block_num AS source_op_block
FROM hive.btracker_app_operations_view ho --- APP specific view must be used, to correctly handle reversible part of the data.
JOIN balance_impacting_ops b ON ho.op_type_id = b.id
JOIN LATERAL
@@ -230,6 +236,7 @@ FOR __balance_change IN
*/
SELECT * FROM hive.get_impacted_balances(ho.body, ho.block_num > 905693)
) bio ON true
JOIN hive.btracker_app_accounts_view av ON av.name = bio.account_name
WHERE ho.block_num BETWEEN _from AND _to
ORDER BY ho.block_num, ho.id
LOOP
@@ -268,19 +275,21 @@ END LOOP;
--4rd pass changing to CTE only on rewards 59.63m
--5rd pass changing to CTE where its possible 59.67m
--changing account string into account_id 49.24m
RAISE NOTICE 'Processing delegations, rewards, savings, withdraws';
FOR ___balance_change IN
WITH raw_ops AS
(
SELECT ov.body AS body,
SELECT (ov.body::jsonb) AS body,
ov.id AS source_op,
ov.block_num as source_op_block,
ov.op_type_id as op_type
FROM hive.btracker_app_operations_view ov
WHERE (ov.op_type_id IN (40,41,62,32,33,34,59,39,4,20,56,60) or
WHERE (ov.op_type_id IN (40,41,62,32,33,34,59,39,4,20,56,60,52,53) or
(ov.op_type_id = 55 and ((ov.body::jsonb)->'value'->>'is_saved_into_hbd_balance')::BOOLEAN = false) or
(ov.op_type_id IN (51,52,63) and ((ov.body::jsonb)->'value'->>'payout_must_be_claimed')::BOOLEAN = true))
(ov.op_type_id IN (51,63) and ((ov.body::jsonb)->'value'->>'payout_must_be_claimed')::BOOLEAN = true))
AND ov.block_num BETWEEN _from AND _to
--delegations (40,41,62)
@@ -340,8 +349,11 @@ CASE ___balance_change.op_type
WHEN 56 THEN
PERFORM btracker_app.process_fill_vesting_withdraw_operation(___balance_change.body);
WHEN 53 THEN
PERFORM btracker_app.process_comment_reward_operation(___balance_change.body);
WHEN 60 THEN
CASE ((___balance_change.body::jsonb)->'value'->>'hardfork_id')::INT
CASE ((___balance_change.body)->'value'->>'hardfork_id')::INT
-- HARDFORK 1
WHEN 1 THEN
FOR _vesting_multiplication IN
@@ -524,4 +536,5 @@ END
$$
;
RESET ROLE;
\ No newline at end of file
RESET ROLE;