Skip to content
Snippets Groups Projects

Prerequisuites to Reputation api support

Merged Prerequisuites to Reputation api support
reputation_api_support into develop
Merged Bartek Wrona requested to merge reputation_api_support into develop
Viewing commit a570ff74
Prev Next
Show latest version
2 files
+ 45
− 68
Compare changes
  • Side-by-side
  • Inline
Files
2
hive/db/schema.py
+ 44
− 67
@@ -1311,10 +1311,6 @@ def setup(db):
db.query_no_return(sql)
sql = """
DROP TYPE IF EXISTS vote_reputation_info CASCADE;
CREATE TYPE vote_reputation_info as (is_new_vote BOOLEAN, existing_rshares BIGINT, author_id INTEGER, voter_id INTEGER);
DROP FUNCTION IF EXISTS process_reputation_data(in _block_num hive_blocks.num%TYPE, in _author hive_accounts.name%TYPE,
in _permlink hive_permlink_data.permlink%TYPE, in _voter hive_accounts.name%TYPE, in _rshares hive_votes.rshares%TYPE)
;
@@ -1323,71 +1319,52 @@ def setup(db):
in _author hive_accounts.name%TYPE, in _permlink hive_permlink_data.permlink%TYPE,
in _voter hive_accounts.name%TYPE, in _rshares hive_votes.rshares%TYPE)
RETURNS void
LANGUAGE 'plpgsql'
AS
$FUNCTION$
DECLARE
__insert_info vote_reputation_info;
__rep_delta BIGINT;
__old_rep_delta BIGINT;
BEGIN
SELECT _rshares >> 6 INTO __rep_delta;
INSERT INTO hive_reputation_data
(author_id, voter_id, permlink, block_num, rshares)
--- Warning DISTINCT is needed here since we have to strict join to hv table and there is really made a CROSS JOIN
--- between ha and hv records (producing 2 duplicated records)
SELECT DISTINCT ha.id as author_id, hv.id as voter_id, _permlink, _block_num, _rshares
LANGUAGE sql
VOLATILE
AS $BODY$
WITH __insert_info AS (
INSERT INTO hive_reputation_data
(author_id, voter_id, permlink, block_num, rshares)
--- Warning DISTINCT is needed here since we have to strict join to hv table and there is really made a CROSS JOIN
--- between ha and hv records (producing 2 duplicated records)
SELECT DISTINCT ha.id as author_id, hv.id as voter_id, _permlink, _block_num, _rshares
FROM hive_accounts ha
JOIN hive_accounts hv ON hv.name = _voter
JOIN hive_posts hp ON hp.author_id = ha.id
JOIN hive_permlink_data hpd ON hp.permlink_id = hpd.id
WHERE hpd.permlink = _permlink
AND ha.name = _author
AND NOT hp.is_paidout --- voting on paidout posts shall have no effect
AND hv.reputation >= 0 --- voter's negative reputation eliminates vote from processing
AND (_rshares >= 0
OR (hv.reputation >= (ha.reputation - COALESCE((SELECT (hrd.rshares >> 6) -- if previous vote was a downvote we need to correct author reputation before current comparison to voter's reputation
FROM hive_reputation_data hrd
WHERE hrd.author_id = ha.id
AND hrd.voter_id=hv.id
AND hrd.permlink=_permlink
AND hrd.rshares < 0), 0)))
)
ON CONFLICT ON CONSTRAINT hive_reputation_data_uk DO
UPDATE SET
rshares = EXCLUDED.rshares
RETURNING (xmax = 0) AS is_new_vote,
(SELECT hrd.rshares
FROM hive_reputation_data hrd
--- Warning we want OLD row here, not both, so we're using old ID to select old one (new record has different value) !!!
WHERE hrd.id = hive_reputation_data.id AND hrd.author_id = author_id and hrd.voter_id=voter_id and hrd.permlink=_permlink) AS old_rshares, author_id, voter_id
)
UPDATE hive_accounts uha
SET reputation = CASE __insert_info.is_new_vote
WHEN true THEN ha.reputation + (_rshares >> 6)
ELSE ha.reputation - (__insert_info.old_rshares >> 6) + (_rshares >> 6)
END
FROM hive_accounts ha
JOIN hive_accounts hv ON hv.name = _voter
JOIN hive_posts hp ON hp.author_id = ha.id
JOIN hive_permlink_data hpd ON hp.permlink_id = hpd.id
WHERE hpd.permlink = _permlink
AND ha.name = _author
AND NOT hp.is_paidout --- voting on paidout posts shall have no effect
AND hv.reputation >= 0 --- voter's negative reputation eliminates vote from processing
AND (_rshares >= 0
OR (hv.reputation >= ha.reputation - COALESCE((SELECT hrd.rshares -- if previous vote was a downvote we need to correct author reputation before current comparison to voter's reputation
FROM hive_reputation_data hrd
WHERE hrd.author_id = ha.id
AND hrd.voter_id=hv.id
AND hrd.permlink=_permlink
AND hrd.rshares < 0), 0))
)
ON CONFLICT ON CONSTRAINT hive_reputation_data_uk DO
UPDATE SET
rshares = EXCLUDED.rshares
RETURNING (xmax = 0) AS is_new_vote,
(SELECT hrd.rshares
FROM hive_reputation_data hrd
--- Warning we want OLD row here, not both !!!
WHERE hrd.id = hive_reputation_data.id AND hrd.author_id = author_id and hrd.voter_id=voter_id and hrd.permlink=_permlink) AS old_rshares, author_id, voter_id
INTO __insert_info
JOIN __insert_info ON ha.id = __insert_info.author_id
WHERE uha.id = __insert_info.author_id
;
IF __insert_info IS NULL THEN
RETURN;
END IF;
IF __insert_info.is_new_vote THEN
UPDATE hive_accounts ha
SET reputation = ha.reputation + __rep_delta
WHERE ha.id = __insert_info.author_id
;
ELSE
SELECT __insert_info.existing_rshares >> 6 INTO __old_rep_delta;
UPDATE hive_accounts ha
SET reputation = ha.reputation - __old_rep_delta + __rep_delta
WHERE ha.id = __insert_info.author_id
;
END IF;
END
$FUNCTION$
;
"""
$BODY$;
"""
db.query_no_return(sql)