faster bridge get trending for develop

hive/db/schema.py

@@ -905,7 +905,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 );
@@ -1025,7 +1025,7 @@ def setup(db):
       LANGUAGE 'plpgsql'
       AS
       $function$
-      DECLARE 
+      DECLARE
         post_id INT;
       BEGIN
         SELECT INTO post_id COALESCE( (SELECT hp.id
@@ -1196,7 +1196,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
@@ -1221,7 +1221,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
@@ -1603,7 +1603,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
@@ -1620,7 +1620,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
@@ -1631,7 +1631,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) !!!
@@ -1685,6 +1685,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
+        $function$
+        language sql
+    """
+    db.query_no_return(sql)
+
 def reset_autovac(db):
     """Initializes/resets per-table autovacuum/autoanalyze params.