Skip to content
Snippets Groups Projects

Various fixes in bridge and hive API

Merged Various fixes in bridge and hive API
limit_checks_and_post_notification_fixes into develop
Merged Andrzej Lisak requested to merge limit_checks_and_post_notification_fixes into develop
Viewing commit 414dc3d8
Next
Show latest version
14 files
+ 348
− 314
Compare changes
  • Side-by-side
  • Inline
Files
14
  • 414dc3d8 
    [ABW]: last_month() moved to common to be shared across APIs (it should also be changed to take month from head block but it was not done yet)
changed checks on numerical values - now empty string is treated as if parameter was skipped; also invalid cast to int no longer generates Server Error, but 'invalid parameters'
[Fix] restored older version of list_top_communities, since most recent change was apparently experimental and introduced hardcoded community id into query; also fixed check on limit there
[Fix] fixed list_pop_communities; it was apparently never used since it crashed and its code did not change since inception
[Fix] added checks on min_score and post_id to post_notifications so they can generate meaningful errors (similar change should be done in account_notifications)
[Fix] post_notifications again reacts correctly on missing post specified in its call parameters (similar change needed in account_notifications for account param)
tabs removed from schema.py
hive/db/schema.py
+ 263
− 233
@@ -1728,192 +1728,210 @@ def setup(db):
*
FROM
(
SELECT --replies
posts_and_scores.block_num as block_num
, posts_and_scores.id as id
, posts_and_scores.type_id as type_id
, posts_and_scores.created_at as created_at
, posts_and_scores.author as src
, posts_and_scores.parent_author as dst
, posts_and_scores.parent_author as author
, posts_and_scores.parent_permlink as permlink
, ''::VARCHAR as community
, ''::VARCHAR as community_title
, ''::VARCHAR as payload
, posts_and_scores.score as score
FROM
(
SELECT
hpv.block_num as block_num
, notification_id(
hpv.block_num
, CASE ( hpv.depth )
WHEN 1 THEN 12 --replies
ELSE 13 --comment replies
END
, hpv.id ) as id
, CASE ( hpv.depth )
WHEN 1 THEN 12 --replies
ELSE 13 --comment replies
END as type_id
, hpv.created_at
, hpv.author
, hpv.parent_author as parent_author
, hpv.parent_permlink_or_category as parent_permlink
, hpv.depth
, hpv.parent_author_id
, hpv.author_id
, harv.score as score
FROM
hive_posts_view hpv
JOIN hive_accounts_rank_view harv ON harv.id = hpv.author_id
WHERE hpv.depth > 0
) as posts_and_scores
WHERE NOT EXISTS(
SELECT 1
FROM
hive_follows hf
WHERE hf.follower = posts_and_scores.parent_author_id AND hf.following = posts_and_scores.author_id AND hf.state = 2
)
UNION ALL
SELECT --follows
hf.block_num as block_num
, notifs_id.notif_id as id
, 15 as type_id
, hf.created_at as created_at
, followers_scores.follower_name as src
, ha2.name as dst
, ''::VARCHAR as author
, ''::VARCHAR as permlink
, ''::VARCHAR as community
, ''::VARCHAR as community_title
, ''::VARCHAR as payload
, followers_scores.score as score
FROM
hive_follows hf
JOIN hive_accounts ha2 ON hf.following = ha2.id
JOIN (
SELECT
ha.id as follower_id
, ha.name as follower_name
, harv.score as score
FROM hive_accounts ha
JOIN hive_accounts_rank_view harv ON harv.id = ha.id
) as followers_scores ON followers_scores.follower_id = hf.follower
JOIN (
SELECT
hf2.id as id
, notification_id(hf2.block_num, 15, hf2.id) as notif_id
FROM hive_follows hf2
) as notifs_id ON notifs_id.id = hf.id
UNION ALL
SELECT --reblogs
hr.block_num as block_num
, hr_scores.notif_id as id
, 14 as type_id
, hr.created_at as created_at
, hr.account as src
, ha.name as dst
, ha.name as author
, hpd.permlink as permlink
, ''::VARCHAR as community
, ''::VARCHAR as community_title
, ''::VARCHAR as payload
, hr_scores.score as score
SELECT --replies
posts_and_scores.block_num as block_num
, posts_and_scores.id as id
, posts_and_scores.post_id as post_id
, posts_and_scores.type_id as type_id
, posts_and_scores.created_at as created_at
, posts_and_scores.author as src
, posts_and_scores.parent_author as dst
, posts_and_scores.parent_author as author
, posts_and_scores.parent_permlink as permlink
, ''::VARCHAR as community
, ''::VARCHAR as community_title
, ''::VARCHAR as payload
, posts_and_scores.score as score
FROM
hive_reblogs hr
JOIN hive_posts hp ON hr.post_id = hp.id
JOIN hive_permlink_data hpd ON hp.permlink_id = hpd.id
JOIN (
SELECT
hr2.id as id
, notification_id(hr2.block_num, 14, hr2.id) as notif_id
, harv.score as score
FROM hive_reblogs hr2
JOIN hive_accounts has ON hr2.account = has.name
JOIN hive_accounts_rank_view harv ON harv.id = has.id
) as hr_scores ON hr_scores.id = hr.id
JOIN hive_accounts ha ON hp.author_id = ha.id
UNION ALL
SELECT --subscriptions
hs.block_num as block_num
, hs_scores.notif_id as id
, 11 as type_id
, hs.created_at as created_at
, hs_scores.src as src
, ha_com.name as dst
, ''::VARCHAR as author
, ''::VARCHAR as permlink
, hc.name as community
, hc.title as community_title
, ''::VARCHAR as payload
, hs_scores.score
(
SELECT
hpv.block_num as block_num
, notification_id(
hpv.block_num
, CASE ( hpv.depth )
WHEN 1 THEN 12 --replies
ELSE 13 --comment replies
END
, hpv.id ) as id
, CASE ( hpv.depth )
WHEN 1 THEN 12 --replies
ELSE 13 --comment replies
END as type_id
, hpv.created_at
, hpv.author
, hpv.parent_id as post_id
, hpv.parent_author as parent_author
, hpv.parent_permlink_or_category as parent_permlink
, hpv.depth
, hpv.parent_author_id
, hpv.author_id
, harv.score as score
FROM
hive_subscriptions hs
JOIN hive_communities hc ON hs.community_id = hc.id
JOIN (
SELECT
hs2.id as id
, notification_id(hs2.block_num, 11, hs2.id) as notif_id
, harv.score as score
, ha.name as src
FROM hive_subscriptions hs2
JOIN hive_accounts ha ON hs2.account_id = ha.id
JOIN hive_accounts_rank_view harv ON harv.id = ha.id
) as hs_scores ON hs_scores.id = hs.id
JOIN hive_accounts ha_com ON hs.community_id = ha_com.id
UNION ALL
SELECT -- new community
hc.block_num as block_num
, hc_id.notif_id as id
, 1 as type_id
, hc.created_at as created_at
, ''::VARCHAR as src
, ha.name as dst
, ''::VARCHAR as author
, ''::VARCHAR as permlink
, hc.name as community
, ''::VARCHAR as community_title
, ''::VARCHAR as payload
, 35 as score
hive_posts_view hpv
JOIN hive_accounts_rank_view harv ON harv.id = hpv.author_id
WHERE hpv.depth > 0
) as posts_and_scores
WHERE NOT EXISTS(
SELECT 1
FROM
hive_communities hc
JOIN hive_accounts ha ON ha.id = hc.id
JOIN (
SELECT
hc2.id as id
, notification_id(hc2.block_num, 11, hc2.id) as notif_id
FROM hive_communities hc2
) as hc_id ON hc_id.id = hc.id
UNION ALL
SELECT --votes
hv.block_num as block_num
, scores.notif_id as id
, 17 as type_id
, hv.last_update as created_at
, scores.src as src
, scores.dst as dst
, scores.dst as author
, scores.permlink as permlink
, ''::VARCHAR as community
, ''::VARCHAR as community_title
, ''::VARCHAR as payload
, scores.score as score
FROM hive_votes hv
JOIN (
SELECT
hv1.id as id
, notification_id(hv1.block_num, 17, CAST( hv1.id as INT) ) as notif_id
, calculate_notify_vote_score( (hpv.payout + hpv.pending_payout), hpv.abs_rshares, hv1.rshares ) as score
, hpv.author as dst
, ha.name as src
, hpv.permlink as permlink
FROM hive_votes hv1
JOIN hive_posts_view hpv ON hv1.post_id = hpv.id
JOIN hive_accounts ha ON ha.id = hv1.voter_id
WHERE hv1.rshares >= 10e9 AND hpv.abs_rshares != 0
) as scores ON scores.id = hv.id
WHERE scores.score > 0
hive_follows hf
WHERE hf.follower = posts_and_scores.parent_author_id AND hf.following = posts_and_scores.author_id AND hf.state = 2
)
UNION ALL
SELECT --follows
hf.block_num as block_num
, notifs_id.notif_id as id
, 0 as post_id
, 15 as type_id
, hf.created_at as created_at
, followers_scores.follower_name as src
, ha2.name as dst
, ''::VARCHAR as author
, ''::VARCHAR as permlink
, ''::VARCHAR as community
, ''::VARCHAR as community_title
, ''::VARCHAR as payload
, followers_scores.score as score
FROM
hive_follows hf
JOIN hive_accounts ha2 ON hf.following = ha2.id
JOIN (
SELECT
ha.id as follower_id
, ha.name as follower_name
, harv.score as score
FROM hive_accounts ha
JOIN hive_accounts_rank_view harv ON harv.id = ha.id
) as followers_scores ON followers_scores.follower_id = hf.follower
JOIN (
SELECT
hf2.id as id
, notification_id(hf2.block_num, 15, hf2.id) as notif_id
FROM hive_follows hf2
) as notifs_id ON notifs_id.id = hf.id
UNION ALL
SELECT --reblogs
hr.block_num as block_num
, hr_scores.notif_id as id
, hp.id as post_id
, 14 as type_id
, hr.created_at as created_at
, hr.account as src
, ha.name as dst
, ha.name as author
, hpd.permlink as permlink
, ''::VARCHAR as community
, ''::VARCHAR as community_title
, ''::VARCHAR as payload
, hr_scores.score as score
FROM
hive_reblogs hr
JOIN hive_posts hp ON hr.post_id = hp.id
JOIN hive_permlink_data hpd ON hp.permlink_id = hpd.id
JOIN (
SELECT
hr2.id as id
, notification_id(hr2.block_num, 14, hr2.id) as notif_id
, harv.score as score
FROM hive_reblogs hr2
JOIN hive_accounts has ON hr2.account = has.name
JOIN hive_accounts_rank_view harv ON harv.id = has.id
) as hr_scores ON hr_scores.id = hr.id
JOIN hive_accounts ha ON hp.author_id = ha.id
UNION ALL
SELECT --subscriptions
hs.block_num as block_num
, hs_scores.notif_id as id
, 0 as post_id
, 11 as type_id
, hs.created_at as created_at
, hs_scores.src as src
, ha_com.name as dst
, ''::VARCHAR as author
, ''::VARCHAR as permlink
, hc.name as community
, hc.title as community_title
, ''::VARCHAR as payload
, hs_scores.score
FROM
hive_subscriptions hs
JOIN hive_communities hc ON hs.community_id = hc.id
JOIN (
SELECT
hs2.id as id
, notification_id(hs2.block_num, 11, hs2.id) as notif_id
, harv.score as score
, ha.name as src
FROM hive_subscriptions hs2
JOIN hive_accounts ha ON hs2.account_id = ha.id
JOIN hive_accounts_rank_view harv ON harv.id = ha.id
) as hs_scores ON hs_scores.id = hs.id
JOIN hive_accounts ha_com ON hs.community_id = ha_com.id
UNION ALL
SELECT -- new community
hc.block_num as block_num
, hc_id.notif_id as id
, 0 as post_id
, 1 as type_id
, hc.created_at as created_at
, ''::VARCHAR as src
, ha.name as dst
, ''::VARCHAR as author
, ''::VARCHAR as permlink
, hc.name as community
, ''::VARCHAR as community_title
, ''::VARCHAR as payload
, 35 as score
FROM
hive_communities hc
JOIN hive_accounts ha ON ha.id = hc.id
JOIN (
SELECT
hc2.id as id
, notification_id(hc2.block_num, 11, hc2.id) as notif_id
FROM hive_communities hc2
) as hc_id ON hc_id.id = hc.id
UNION ALL
SELECT --votes
hv.block_num as block_num
, scores.notif_id as id
, scores.post_id as post_id
, 17 as type_id
, hv.last_update as created_at
, scores.src as src
, scores.dst as dst
, scores.dst as author
, scores.permlink as permlink
, ''::VARCHAR as community
, ''::VARCHAR as community_title
, ''::VARCHAR as payload
, scores.score as score
FROM hive_votes hv
JOIN (
SELECT
hv1.id as id
, hpv.id as post_id
, notification_id(hv1.block_num, 17, CAST( hv1.id as INT) ) as notif_id
, calculate_notify_vote_score( (hpv.payout + hpv.pending_payout), hpv.abs_rshares, hv1.rshares ) as score
, hpv.author as dst
, ha.name as src
, hpv.permlink as permlink
FROM hive_votes hv1
JOIN hive_posts_view hpv ON hv1.post_id = hpv.id
JOIN hive_accounts ha ON ha.id = hv1.voter_id
WHERE hv1.rshares >= 10e9 AND hpv.abs_rshares != 0
) as scores ON scores.id = hv.id
WHERE scores.score > 0
) as notifs
"""
db.query_no_return(sql)
@@ -2025,17 +2043,17 @@ def setup(db):
;
CREATE TYPE notification AS
(
id BIGINT
, type_id SMALLINT
, created_at TIMESTAMP
, src VARCHAR
, dst VARCHAR
, author VARCHAR
, permlink VARCHAR
, community VARCHAR
, community_title VARCHAR
, payload VARCHAR
, score SMALLINT
id BIGINT
, type_id SMALLINT
, created_at TIMESTAMP
, src VARCHAR
, dst VARCHAR
, author VARCHAR
, permlink VARCHAR
, community VARCHAR
, community_title VARCHAR
, payload VARCHAR
, score SMALLINT
);
"""
db.query_no_return(sql)
@@ -2047,24 +2065,24 @@ def setup(db):
RETURNS SETOF notification
AS
$function$
SELECT
hnv.id
, CAST( hnv.type_id as SMALLINT) as type_id
, hnv.created_at
, hnv.src
, hnv.dst
, hnv.author
, hnv.permlink
, hnv.community
, hnv.community_title
, hnv.payload
, CAST( hnv.score as SMALLINT) as score
FROM
hive_notifications_view hnv
WHERE hnv.block_num > ( SELECT num as head_block FROM hive_blocks ORDER BY num DESC LIMIT 1 ) - (90 * 24 * 3600 / 3) -- 90 days in blocks
AND hnv.dst = _account AND hnv.score >= _min_score AND ( _last_id = -1 OR hnv.id < _last_id )
ORDER BY hnv.id DESC LIMIT _limit
;
SELECT
hnv.id
, CAST( hnv.type_id as SMALLINT) as type_id
, hnv.created_at
, hnv.src
, hnv.dst
, hnv.author
, hnv.permlink
, hnv.community
, hnv.community_title
, hnv.payload
, CAST( hnv.score as SMALLINT) as score
FROM
hive_notifications_view hnv
WHERE hnv.block_num > ( SELECT num as head_block FROM hive_blocks ORDER BY num DESC LIMIT 1 ) - (90 * 24 * 3600 / 3) -- 90 days in blocks
AND hnv.dst = _account AND hnv.score >= _min_score AND ( _last_id = -1 OR hnv.id < _last_id )
ORDER BY hnv.id DESC LIMIT _limit
;
$function$
LANGUAGE sql STABLE
;
@@ -2073,32 +2091,44 @@ def setup(db):
sql = """
DROP FUNCTION IF EXISTS post_notifications
;
;
CREATE OR REPLACE FUNCTION post_notifications(in _author VARCHAR, in _permlink VARCHAR, in _min_score SMALLINT, in _last_id BIGINT, in _limit SMALLINT)
RETURNS SETOF notification
AS
$function$
SELECT
hnv.id
, CAST( hnv.type_id as SMALLINT) as type_id
, hnv.created_at
, hnv.src
, hnv.dst
, hnv.author
, hnv.permlink
, hnv.community
, hnv.community_title
, hnv.payload
, CAST( hnv.score as SMALLINT) as score
FROM
hive_notifications_view hnv
WHERE
hnv.block_num > ( SELECT num as head_block FROM hive_blocks ORDER BY num DESC LIMIT 1 ) - (90 * 24 * 3600 / 3) -- 90 days in blocks
AND hnv.author = _author AND hnv.permlink = _permlink AND hnv.score >= _min_score AND ( _last_id = -1 OR hnv.id < _last_id )
ORDER BY hnv.id DESC LIMIT _limit
;
DECLARE
__post_id INT;
__start_block INT;
BEGIN
__post_id = find_comment_id(_author, _permlink, True);
__start_block = ( SELECT num AS head_block FROM hive_blocks ORDER BY num DESC LIMIT 1 ) - (90 * 24 * 3600 / 3); -- 90 days in blocks
RETURN QUERY
(
SELECT
hnv.id
, CAST( hnv.type_id as SMALLINT) as type_id
, hnv.created_at
, hnv.src
, hnv.dst
, hnv.author
, hnv.permlink
, hnv.community
, hnv.community_title
, hnv.payload
, CAST( hnv.score as SMALLINT) as score
FROM
hive_notifications_view hnv
WHERE
hnv.block_num > __start_block
AND hnv.post_id = __post_id
AND hnv.score >= _min_score
AND ( _last_id = -1 OR hnv.id < _last_id )
ORDER BY hnv.id DESC
LIMIT _limit
);
END
$function$
LANGUAGE sql STABLE
LANGUAGE plpgsql STABLE
"""
db.query_no_return(sql)