RE: How SPS Stakeholders and Staking HP and HBD?

avatar
(Edited)

You are viewing a single comment's thread:

@beaker007
I saw your code. You might want to use hafbe_bal.balance_history_by_day and hafbe_bal.saving_history_by_day to get the data faster. There is also _by_month for both of them.

You can also contact me if you need anything.

hive-169191
0
0
0.000
11 comments
avatar

Hi thanks for dropping a comment,

I have took a look on those tables. Do you also got some documentation.
Because for me its not clear what the balance is.

balance_history_by_day i assume that is the HP in VESTS?
saving_history_by_day is that the hive saving in vest or hdb saving.

That is why i like the balance_history view a bit more because that a name that i know 😁. and it does the account name mapping directly.

also in my react code i did a call a bit smarter to limit the result directly.

            SELECT * FROM (
                SELECT 
                    bh.*,
                    hb.timestamp as block_timestamp,
                    ROW_NUMBER() OVER (PARTITION BY bh.account_name ORDER BY bh.block_num ASC) AS rn_asc,
                    ROW_NUMBER() OVER (PARTITION BY bh.account_name ORDER BY bh.block_num DESC) AS rn_desc
                FROM hafsql.balances_history bh
                LEFT JOIN hafsql.haf_blocks hb ON bh.block_num = hb.block_num
                WHERE bh.account_name IN (${placeholders})
            ) sub
            WHERE rn_asc = 1 -- first row per account
               OR rn_desc = 1 -- last row per account
               OR (block_num % 100 = 0) -- every 100th block
            ORDER BY account_name, block_num DESC

the python wrapper whas a bit rushed coding, and did not do the partition well, but i like to learn end i might end up using the two tables you recommend.

Lots of data to go trough but really nice you create/maintaining the hafSQL looks really impressive 👏

0
0
0.000
Reply
avatar

This query seems better but still takes too long for "spammy" accounts. It's fine though.

Those tables are not created by me. They are from haf balance tracker. Those tables store each balance separated by their symbol aka nai. So for HIVE balance you have one row and another row for HBD balance. It would be storing VESTS so your can use vests_to_hive function.

Anyway, I plan on dropping balances from hafsql and depending on balance tracker. I can probably recreate the same view you are using so shouldn't be any big breaking changes. I should probably create more views and take advantage of other HAF apps/tables including the two mentioned tables.

0
0
0.000
Reply
avatar

Nice info we are getting closer... if this better I could start using this part of the tables.

Only now i'm missing the delegated do you know why that is not part of the VESTS balance?

WITH nai_map (nai, asset_name) AS (
  VALUES
    (21, 'HIVE'),
    (13, 'HBD'),
    (37, 'VESTS')
)
SELECT 
    a.name AS account_name,
    x.updated_at,
    x.source_op,
    x.source_op_block,
    -- nm.asset_name,
    -- x.balance,
    /* Convert to HIVE only when the asset is VESTS */
    CASE 
      WHEN nm.asset_name = 'VESTS' 
        THEN hafsql.vests_to_hive(x.balance/1e6,  a.block_num)
      ELSE x.balance/1e3
    END AS balance,
    /* The unit of balance_value */
    CASE 
      WHEN nm.asset_name = 'VESTS' THEN 'HP'
      ELSE nm.asset_name
    END AS asset,
    s.balance AS saving
FROM hafbe_bal.balance_history_by_month x
JOIN hafd.accounts a 
  ON x.account = a.id
LEFT JOIN hafbe_bal.saving_history_by_month s
  ON s.account    = x.account
 AND s.updated_at = x.updated_at
 AND s.nai        = x.nai
LEFT JOIN nai_map nm
  ON x.nai = nm.nai
WHERE a.name = 'beaker007'
ORDER BY x.updated_at DESC;

image.png

0
0
0.000
Reply
avatar

There is no historical tracking of delegations. There are only current values available.

0
0
0.000
Reply
avatar

Oke clear. you cannot have everything 😁 some limitation over performance that is the question.

@azricon, how important do think it is to include the delegations?

0
0
0.000
Reply
avatar

I can take a look later to see if I can come up with a query that tracks historical delegations and make that a materialized view which can refresh once an hour or so depending on the query.

0
0
0.000
Reply
avatar

Do not what to spam you 😂. But i found a another method to limit the call that i will commit for now

This does the first last record and every first of the month, exactly what is want

WITH bh AS (
  SELECT
    bh.*,
    hb.timestamp AS block_timestamp,
    date_trunc('month', hb.timestamp) AS month_start,
    ROW_NUMBER() OVER (
      PARTITION BY bh.account_name
      ORDER BY bh.block_num ASC
    ) AS rn_first,
    ROW_NUMBER() OVER (
      PARTITION BY bh.account_name
      ORDER BY bh.block_num DESC
    ) AS rn_last,
    ROW_NUMBER() OVER (
      PARTITION BY bh.account_name, date_trunc('month', hb.timestamp)
      ORDER BY hb.timestamp ASC, bh.block_num ASC
    ) AS rn_month
  FROM hafsql.balances_history bh
  JOIN hafsql.haf_blocks hb
    ON hb.block_num = bh.block_num
  WHERE bh.account_name IN ({placeholders})
)
SELECT
  *
FROM bh
WHERE rn_first = 1
   OR rn_last  = 1
   OR rn_month = 1
ORDER BY account_name, block_num DESC;

limited the number of account at once to 5

Thanks again for the help 🙏

0
0
0.000
Reply
avatar

You are not spamming. This one takes too long for top witnesses.

0
0
0.000
Reply
avatar

BTW: what i also can do easily is limit the request one account at the time

0
0
0.000
Reply
avatar

There is a limit on how many concurrent connections you can have per IP. Currently it is set to 6. So it's not a problem on the database side but your will get errors so you should probably setup a pool on the client side and run queries on the pool.

0
0
0.000
Reply
avatar

oke thanks again just updated my pool to 6. it was on default 10

0
0
0.000
Reply