Various fixes in bridge and hive API

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

@@ -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)