list_comments api call improvements

• Improved sql for list_comments
• Removed materialized view from schema: it was not used, it required expensive refreshes
• Some calls are slow comparing to original hived calls
• by_cashout, by_update and by_author_update results are different than expected (waiting for merge from #47 (closed))
• fixed manual tests for list_comments
• Posting a comment will now update active but active is not propagating up at the moment,
• Added post object for database api post
• Added a possibility to convert amount notation to nai notation
• Other minor fixes

hive/db/schema.py

@@ -648,6 +648,7 @@ def setup(db):
             hc.title AS community_title,
             hc.name AS community_name,
             hp.abs_rshares,
+            hp.cashout_time,
             hp.max_cashout_time,
             hp.reward_weight
             FROM hive_posts hp
@@ -732,6 +733,298 @@ def setup(db):
     """
     db.query_no_return(sql)
 
+    sql = """
+      DROP TYPE IF EXISTS database_api_post CASCADE;
+      CREATE TYPE database_api_post AS (
+        id INT, 
+        community_id INT,
+        author VARCHAR(16),
+        permlink VARCHAR(255),
+        title VARCHAR(512),
+        body TEXT,
+        category VARCHAR(255),
+        depth SMALLINT,
+        promoted DECIMAL(10,3),
+        payout DECIMAL(10,3),
+        payout_at TIMESTAMP,
+        is_paidout BOOLEAN,
+        children SMALLINT,
+        votes INT,
+        created_at TIMESTAMP,
+        updated_at TIMESTAMP,
+        rshares NUMERIC,
+        json TEXT,
+        is_hidden BOOLEAN,
+        is_grayed BOOLEAN,
+        total_votes INT,
+        flag_weight REAL,
+        parent_author VARCHAR(16),
+        parent_permlink VARCHAR(255),
+        curator_payout_value VARCHAR(30),
+        root_author VARCHAR(16),
+        root_permlink VARCHAR(255),
+        max_accepted_payout VARCHAR(30),
+        percent_hbd INT,
+        allow_replies BOOLEAN,
+        allow_votes BOOLEAN,
+        allow_curation_rewards BOOLEAN,
+        beneficiaries JSON,
+        url TEXT,
+        root_title VARCHAR(512),
+        abs_rshares BIGINT,
+        active TIMESTAMP,
+        author_rewards BIGINT,
+        max_cashout_time TIMESTAMP,
+        reward_weight INT
+      )
+      ;
+
+      DROP FUNCTION IF EXISTS list_comments_by_cashout_time(timestamp, character varying, character varying, int)
+      ;
+      CREATE OR REPLACE FUNCTION list_comments_by_cashout_time(
+        in _cashout_time timestamp,
+        in _author hive_accounts.name%TYPE,
+        in _permlink hive_permlink_data.permlink%TYPE,
+        in _limit INT)
+        RETURNS SETOF database_api_post
+        AS
+        $function$
+        BEGIN
+          RETURN QUERY 
+          SELECT
+              hp.id, hp.community_id, hp.author, hp.permlink, hp.title, hp.body,
+              hp.category, hp.depth, hp.promoted, hp.payout, hp.payout_at, hp.is_paidout,
+              hp.children, hp.votes, hp.created_at, hp.updated_at, hp.rshares, hp.json,
+              hp.is_hidden, hp.is_grayed, hp.total_votes, hp.flag_weight, hp.parent_author,
+              hp.parent_permlink, hp.curator_payout_value, hp.root_author, hp.root_permlink,
+              hp.max_accepted_payout, hp.percent_hbd, hp.allow_replies, hp.allow_votes,
+              hp.allow_curation_rewards, hp.beneficiaries, hp.url, hp.root_title, hp.abs_rshares,
+              hp.active, hp.author_rewards, hp.max_cashout_time, hp.reward_weight
+          FROM
+              hive_posts_view hp
+          WHERE
+              NOT hp.is_muted AND
+              NOT hp.is_deleted AND
+              hp.cashout_time >= _cashout_time AND
+              hp.id >= (SELECT id FROM hive_posts_view hp1 WHERE hp1.author >= _author AND hp1.permlink >= _permlink ORDER BY id LIMIT 1)
+          ORDER BY
+              hp.cashout_time ASC,
+              hp.id ASC
+          LIMIT
+              _limit
+          ;
+        END
+        $function$
+        LANGUAGE plpgsql
+      ;
+
+      DROP FUNCTION IF EXISTS list_comments_by_permlink(character varying, character varying, int)
+      ;
+      CREATE OR REPLACE FUNCTION list_comments_by_permlink(
+        in _author hive_accounts.name%TYPE,
+        in _permlink hive_permlink_data.permlink%TYPE,
+        in _limit INT)
+        RETURNS SETOF database_api_post
+        AS
+        $function$
+        BEGIN
+          RETURN QUERY 
+          SELECT
+              hp.id, hp.community_id, hp.author, hp.permlink, hp.title, hp.body,
+              hp.category, hp.depth, hp.promoted, hp.payout, hp.payout_at, hp.is_paidout,
+              hp.children, hp.votes, hp.created_at, hp.updated_at, hp.rshares, hp.json,
+              hp.is_hidden, hp.is_grayed, hp.total_votes, hp.flag_weight, hp.parent_author,
+              hp.parent_permlink, hp.curator_payout_value, hp.root_author, hp.root_permlink,
+              hp.max_accepted_payout, hp.percent_hbd, hp.allow_replies, hp.allow_votes,
+              hp.allow_curation_rewards, hp.beneficiaries, hp.url, hp.root_title, hp.abs_rshares,
+              hp.active, hp.author_rewards, hp.max_cashout_time, hp.reward_weight
+          FROM
+              hive_posts_view hp
+          WHERE
+              NOT hp.is_muted AND
+              NOT hp.is_deleted AND
+              hp.author >= _author COLLATE "C" AND
+              hp.permlink >= _permlink COLLATE "C"
+          ORDER BY
+              hp.author COLLATE "C" ASC,
+              hp.permlink COLLATE "C" ASC
+          LIMIT
+              _limit
+          ;
+        END
+        $function$
+        LANGUAGE plpgsql
+      ;
+
+      DROP FUNCTION IF EXISTS list_comments_by_root(character varying, character varying, character varying, character varying, int)
+      ;
+      CREATE OR REPLACE FUNCTION list_comments_by_root(
+        in _root_author hive_accounts.name%TYPE,
+        in _root_permlink hive_permlink_data.permlink%TYPE,
+        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
+        AS
+        $function$
+        BEGIN
+          RETURN QUERY 
+          SELECT
+              hp.id, hp.community_id, hp.author, hp.permlink, hp.title, hp.body,
+              hp.category, hp.depth, hp.promoted, hp.payout, hp.payout_at, hp.is_paidout,
+              hp.children, hp.votes, hp.created_at, hp.updated_at, hp.rshares, hp.json,
+              hp.is_hidden, hp.is_grayed, hp.total_votes, hp.flag_weight, hp.parent_author,
+              hp.parent_permlink, hp.curator_payout_value, hp.root_author, hp.root_permlink,
+              hp.max_accepted_payout, hp.percent_hbd, hp.allow_replies, hp.allow_votes,
+              hp.allow_curation_rewards, hp.beneficiaries, hp.url, hp.root_title, hp.abs_rshares,
+              hp.active, hp.author_rewards, hp.max_cashout_time, hp.reward_weight
+          FROM
+              hive_posts_view hp
+          WHERE
+              NOT hp.is_muted AND
+              NOT hp.is_deleted AND
+              root_author >= _root_author AND
+              root_permlink >= _root_permlink AND
+              hp.id >= (SELECT id FROM hive_posts_view hp1 WHERE hp1.author >= _start_post_author AND hp1.permlink >= _start_post_permlink ORDER BY id LIMIT 1)
+          ORDER BY 
+              root_author ASC, 
+              root_permlink ASC,
+              id ASC
+          LIMIT 
+              _limit
+          ;
+        END
+        $function$
+        LANGUAGE plpgsql
+      ;
+
+      DROP FUNCTION IF EXISTS list_comments_by_parent(character varying, character varying, character varying, character varying, int)
+      ;
+      CREATE OR REPLACE FUNCTION list_comments_by_parent(
+        in _parent_author hive_accounts.name%TYPE,
+        in _parent_permlink hive_permlink_data.permlink%TYPE,
+        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
+        AS
+        $function$
+        BEGIN
+          RETURN QUERY 
+          SELECT
+              hp.id, hp.community_id, hp.author, hp.permlink, hp.title, hp.body,
+              hp.category, hp.depth, hp.promoted, hp.payout, hp.payout_at, hp.is_paidout,
+              hp.children, hp.votes, hp.created_at, hp.updated_at, hp.rshares, hp.json,
+              hp.is_hidden, hp.is_grayed, hp.total_votes, hp.flag_weight, hp.parent_author,
+              hp.parent_permlink, hp.curator_payout_value, hp.root_author, hp.root_permlink,
+              hp.max_accepted_payout, hp.percent_hbd, hp.allow_replies, hp.allow_votes,
+              hp.allow_curation_rewards, hp.beneficiaries, hp.url, hp.root_title, hp.abs_rshares,
+              hp.active, hp.author_rewards, hp.max_cashout_time, hp.reward_weight
+          FROM
+              hive_posts_view hp
+          WHERE
+              NOT hp.is_muted AND
+              NOT hp.is_deleted AND
+              parent_author >= _parent_author AND
+              parent_permlink >= _parent_permlink AND
+              hp.id >= (SELECT id FROM hive_posts_view hp1 WHERE hp1.author >= _start_post_author AND hp1.permlink >= _start_post_permlink ORDER BY id LIMIT 1)
+          ORDER BY
+              parent_author ASC,
+              parent_permlink ASC,
+              id ASC
+          LIMIT
+              _limit
+          ;
+        END
+        $function$
+        LANGUAGE plpgsql
+      ;
+
+      DROP FUNCTION IF EXISTS list_comments_by_last_update(character varying, timestamp, character varying, character varying, int)
+      ;
+      CREATE OR REPLACE FUNCTION list_comments_by_last_update(
+        in _parent_author hive_accounts.name%TYPE,
+        in _updated_at hive_posts.updated_at%TYPE,
+        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
+        AS
+        $function$
+        BEGIN
+          RETURN QUERY 
+          SELECT
+              hp.id, hp.community_id, hp.author, hp.permlink, hp.title, hp.body,
+              hp.category, hp.depth, hp.promoted, hp.payout, hp.payout_at, hp.is_paidout,
+              hp.children, hp.votes, hp.created_at, hp.updated_at, hp.rshares, hp.json,
+              hp.is_hidden, hp.is_grayed, hp.total_votes, hp.flag_weight, hp.parent_author,
+              hp.parent_permlink, hp.curator_payout_value, hp.root_author, hp.root_permlink,
+              hp.max_accepted_payout, hp.percent_hbd, hp.allow_replies, hp.allow_votes,
+              hp.allow_curation_rewards, hp.beneficiaries, hp.url, hp.root_title, hp.abs_rshares,
+              hp.active, hp.author_rewards, hp.max_cashout_time, hp.reward_weight
+          FROM
+              hive_posts_view hp
+          WHERE
+              NOT hp.is_muted AND
+              NOT hp.is_deleted AND
+              hp.parent_author >= _parent_author AND 
+              hp.updated_at >= _updated_at AND 
+              hp.id >= (SELECT id FROM hive_posts_view hp1 WHERE hp1.author >= _start_post_author AND hp1.permlink >= _start_post_permlink ORDER BY id LIMIT 1)
+          ORDER BY 
+              hp.parent_author ASC, 
+              hp.updated_at ASC, 
+              hp.id ASC 
+          LIMIT 
+              _limit
+          ;
+        END
+        $function$
+        LANGUAGE plpgsql
+      ;
+      DROP FUNCTION IF EXISTS list_comments_by_author_last_update(character varying, timestamp, character varying, character varying, int)
+      ;
+      CREATE OR REPLACE FUNCTION list_comments_by_author_last_update(
+        in _author hive_accounts.name%TYPE,
+        in _updated_at hive_posts.updated_at%TYPE,
+        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
+        AS
+        $function$
+        BEGIN
+          RETURN QUERY 
+          SELECT
+              hp.id, hp.community_id, hp.author, hp.permlink, hp.title, hp.body,
+              hp.category, hp.depth, hp.promoted, hp.payout, hp.payout_at, hp.is_paidout,
+              hp.children, hp.votes, hp.created_at, hp.updated_at, hp.rshares, hp.json,
+              hp.is_hidden, hp.is_grayed, hp.total_votes, hp.flag_weight, hp.parent_author,
+              hp.parent_permlink, hp.curator_payout_value, hp.root_author, hp.root_permlink,
+              hp.max_accepted_payout, hp.percent_hbd, hp.allow_replies, hp.allow_votes,
+              hp.allow_curation_rewards, hp.beneficiaries, hp.url, hp.root_title, hp.abs_rshares,
+              hp.active, hp.author_rewards, hp.max_cashout_time, hp.reward_weight
+          FROM
+              hive_posts_view hp
+          WHERE
+              NOT hp.is_muted AND
+              NOT hp.is_deleted AND
+              hp.author >= _author AND 
+              hp.updated_at >= _updated_at AND 
+              hp.id >= (SELECT id FROM hive_posts_view hp1 WHERE hp1.author >= _start_post_author AND hp1.permlink >= _start_post_permlink ORDER BY id LIMIT 1)
+          ORDER BY 
+              hp.parent_author ASC, 
+              hp.updated_at ASC, 
+              hp.id ASC 
+          LIMIT 
+              _limit
+          ;
+        END
+        $function$
+        LANGUAGE plpgsql
+      ;
+    """
+    db.query_no_return(sql)
+
 def reset_autovac(db):
     """Initializes/resets per-table autovacuum/autoanalyze params.