find_votes and list_votes fixes

SQL function get_account renamed find_account_id to follow naming scheme.

Vote related views/types/functions extended by id since database_api requires it.

New SQL function to cover find_votes functionality.

[Fix]: Neither voter nor post is mandatory in list_votes calls.

[Fix]: fixed internal _get_post_id in bridge_api

find_votes_impl no longer requires full context, just what it actually uses: db; it also no longer checks passed arguments, only its official API version does.

list_votes_impl removed since only its official version is needed

[Fix]: Official API find_votes/list_votes now properly pack response in dictionary with 'votes' list

hive/db/schema.py

@@ -62,7 +62,7 @@ def build_metadata():
 
         sa.UniqueConstraint('name', name='hive_accounts_ux1'),
         sa.Index('hive_accounts_ix5', 'cached_at'), # core/listen sweep
-	sa.Index('hive_accounts_ix6', 'reputation')
+        sa.Index('hive_accounts_ix6', 'reputation')
     )
 
 
@@ -477,7 +477,7 @@ def setup(db):
       LANGUAGE 'plpgsql'
       AS
       $function$
-      DECLARE 
+      DECLARE
         post_id INT;
       BEGIN
         SELECT INTO post_id COALESCE( (SELECT hp.id
@@ -506,7 +506,7 @@ def setup(db):
         LANGUAGE 'plpgsql'
         AS
         $function$
-        DECLARE 
+        DECLARE
           account_id INT;
         BEGIN
           SELECT INTO account_id COALESCE( ( SELECT id FROM hive_accounts WHERE name=_account ), 0 );
@@ -687,26 +687,26 @@ def setup(db):
           ) post_count,
           created_at,
           (
-          	SELECT GREATEST
-          	(
-          		created_at,
-		          COALESCE(
-		            (
-		              select max(hp.created_at)
-		              FROM hive_posts hp
-		              WHERE ha.id=hp.author_id
-		            ),
-		            '1970-01-01 00:00:00.0'
-		          ),
-		          COALESCE(
-		            (
-		              select max(hv.last_update)
-		              from hive_votes hv
-		              WHERE ha.id=hv.voter_id
-		            ),
-		            '1970-01-01 00:00:00.0'
-		          )
-          	)
+            SELECT GREATEST
+            (
+              created_at,
+              COALESCE(
+                (
+                  select max(hp.created_at)
+                  FROM hive_posts hp
+                  WHERE ha.id=hp.author_id
+                ),
+                '1970-01-01 00:00:00.0'
+              ),
+              COALESCE(
+                (
+                  select max(hv.last_update)
+                  from hive_votes hv
+                  WHERE ha.id=hv.voter_id
+                ),
+                '1970-01-01 00:00:00.0'
+              )
+            )
           ) active_at,
           display_name,
           about,
@@ -1241,7 +1241,7 @@ def setup(db):
             hp.allow_curation_rewards, hp.beneficiaries, hp.url, hp.root_title, hp.abs_rshares,
             hp.active, hp.author_rewards
           FROM hive_posts_view hp
-          INNER JOIN 
+          INNER JOIN
           (
           SELECT hp2.id, hp2.root_id FROM hive_posts hp2
           WHERE NOT hp2.is_muted
@@ -1266,7 +1266,7 @@ def setup(db):
         in _start_post_author hive_accounts.name%TYPE,
         in _start_post_permlink hive_permlink_data.permlink%TYPE,
         in _limit INT)
-        RETURNS SETOF database_api_post 
+        RETURNS SETOF database_api_post
         LANGUAGE sql
         COST 100
         STABLE
@@ -1648,7 +1648,7 @@ def setup(db):
             in _voter hive_accounts.name%TYPE, in _rshares hive_votes.rshares%TYPE)
           RETURNS void
           LANGUAGE sql
-          VOLATILE 
+          VOLATILE
           AS $BODY$
             WITH __insert_info AS (
               INSERT INTO hive_reputation_data
@@ -1665,7 +1665,7 @@ def setup(db):
 
                     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 
+                    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
@@ -1676,7 +1676,7 @@ def setup(db):
               ON CONFLICT ON CONSTRAINT hive_reputation_data_uk DO
               UPDATE SET
                 rshares = EXCLUDED.rshares
-              RETURNING (xmax = 0) AS is_new_vote, 
+              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) !!!
@@ -1721,8 +1721,8 @@ def setup(db):
         $function$
         BEGIN
         RETURN CAST( _block_number as BIGINT ) << 32
-        	  | ( _notifyType << 16 )
-        	  | ( _id & CAST( x'00FF' as INTEGER) );
+               | ( _notifyType << 16 )
+               | ( _id & CAST( x'00FF' as INTEGER) );
         END
         $function$
         LANGUAGE plpgsql IMMUTABLE
@@ -1730,6 +1730,106 @@ def setup(db):
     """
     db.query_no_return(sql)
 
+
+    sql = """
+        DROP TYPE IF EXISTS bridge_api_post CASCADE;
+        CREATE TYPE bridge_api_post AS (
+            id INTEGER,
+            author VARCHAR,
+            parent_author VARCHAR,
+            author_rep FLOAT4,
+            root_title VARCHAR,
+            beneficiaries JSON,
+            max_accepted_payout VARCHAR,
+            percent_hbd INTEGER,
+            url TEXT,
+            permlink VARCHAR,
+            parent_permlink_or_category VARCHAR,
+            title VARCHAR,
+            body TEXT,
+            category VARCHAR,
+            depth SMALLINT,
+            promoted DECIMAL(10,3),
+            payout DECIMAL(10,3),
+            pending_payout DECIMAL(10,3),
+            payout_at TIMESTAMP,
+            is_paidout BOOLEAN,
+            children INTEGER,
+            votes INTEGER,
+            created_at TIMESTAMP,
+            updated_at TIMESTAMP,
+            rshares NUMERIC,
+            abs_rshares NUMERIC,
+            json TEXT,
+            is_hidden BOOLEAN,
+            is_grayed BOOLEAN,
+            total_votes BIGINT,
+            sc_trend FLOAT4,
+            role_title VARCHAR,
+            community_title VARCHAR,
+            role_id SMALLINT,
+            is_pinned BOOLEAN,
+            curator_payout_value VARCHAR
+        );
+    """
+    db.query_no_return(sql)
+
+    sql = """
+        DROP FUNCTION IF EXISTS bridge_get_ranked_post_by_trends;
+        CREATE FUNCTION bridge_get_ranked_post_by_trends( in _limit SMALLINT )
+        RETURNS SETOF bridge_api_post
+        AS
+        $function$
+        SELECT
+            hp.id,
+            hp.author,
+            hp.parent_author,
+            hp.author_rep,
+            hp.root_title,
+            hp.beneficiaries,
+            hp.max_accepted_payout,
+            hp.percent_hbd,
+            hp.url,
+            hp.permlink,
+            hp.parent_permlink_or_category,
+            hp.title,
+            hp.body,
+            hp.category,
+            hp.depth,
+            hp.promoted,
+            hp.payout,
+            hp.pending_payout,
+            hp.payout_at,
+            hp.is_paidout,
+            hp.children,
+            hp.votes,
+            hp.created_at,
+            hp.updated_at,
+            hp.rshares,
+            hp.abs_rshares,
+            hp.json,
+            hp.is_hidden,
+            hp.is_grayed,
+            hp.total_votes,
+            hp.sc_trend,
+            hp.role_title,
+            hp.community_title,
+            hp.role_id,
+            hp.is_pinned,
+            hp.curator_payout_value
+        FROM
+        (
+            SELECT
+              hp1.id
+            , hp1.sc_trend as trend
+            FROM hive_posts hp1 WHERE NOT hp1.is_paidout AND hp1.depth = 0 ORDER BY hp1.sc_trend DESC LIMIT _limit
+        ) as trends
+        JOIN hive_posts_view hp ON hp.id = trends.id ORDER BY trends.trend DESC
+        $function$
+        language sql
+    """
+    db.query_no_return(sql)
+
 def reset_autovac(db):
     """Initializes/resets per-table autovacuum/autoanalyze params.
 
@@ -1788,13 +1888,13 @@ def set_logged_table_attribute(db, logged):
         db.query_no_return(sql.format(table, 'LOGGED' if logged else 'UNLOGGED'))
 
 def execute_sql_script(query_executor, path_to_script):
-    """ Load and execute sql script from file 
+    """ Load and execute sql script from file
         Params:
           query_executor - callable to execute query with
           path_to_script - path to script
         Returns:
           depending on query_executor
-          
+
         Example:
           print(execute_sql_script(db.query_row, "./test.sql"))
           where test_sql: SELECT * FROM hive_state WHERE block_num = 0;
@@ -1809,4 +1909,4 @@ def execute_sql_script(query_executor, path_to_script):
     except Exception as ex:
         log.exception("Error running sql script: {}".format(ex))
         raise ex
-    return None
\ No newline at end of file
+    return None