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

@@ -118,12 +118,12 @@ def build_metadata():
         sa.Column('abs_rshares', sa.BigInteger, nullable=False, server_default='0'),
         sa.Column('vote_rshares', sa.BigInteger, nullable=False, server_default='0'),
         sa.Column('net_votes', sa.Integer, nullable=False, server_default='0'),
-        sa.Column('active', sa.DateTime, nullable=False, server_default='1970-01-01 00:00:00'),
+        sa.Column('active', sa.DateTime, nullable=False, server_default='1970-01-01 00:00:00'), # TODO: the last time this post was "touched" by voting or reply
         sa.Column('last_payout', sa.DateTime, nullable=False, server_default='1970-01-01 00:00:00'),
         sa.Column('cashout_time', sa.DateTime, nullable=False, server_default='1970-01-01 00:00:00'),
         sa.Column('max_cashout_time', sa.DateTime, nullable=False, server_default='1970-01-01 00:00:00'),
         sa.Column('percent_hbd', sa.Integer, nullable=False, server_default='10000'),
-        sa.Column('reward_weight', sa.Integer, nullable=False, server_default='0'),
+        sa.Column('reward_weight', sa.Integer, nullable=False, server_default='10000'), # Seems to be always 10000
 
         sa.Column('parent_author_id', sa.Integer, nullable=False),
         sa.Column('parent_permlink_id', sa.BigInteger, nullable=False),
@@ -155,6 +155,11 @@ def build_metadata():
         sa.Index('hive_posts_sc_trend_idx', 'sc_trend'),
         sa.Index('hive_posts_sc_hot_idx', 'sc_hot'),
         sa.Index('hive_posts_created_at_idx', 'created_at'),
+
+        sa.Index('hive_posts_root_author_id', 'root_author_id'),
+        sa.Index('hive_posts_root_permlink_id', 'root_permlink_id'),
+        sa.Index('hive_posts_parent_author_id', 'parent_author_id'),
+        sa.Index('hive_posts_parent_permlink_id', 'parent_permlink_id')
     )
 
     sa.Table(
@@ -442,12 +447,31 @@ def setup(db):
           ON CONFLICT DO NOTHING
           ;
           if _parent_author != '' THEN
+            -- update active flag
+            UPDATE
+              hive_posts hpm
+            SET
+              active = _date
+            WHERE
+              hpm.id = (
+                SELECT 
+                  hp.id 
+                FROM 
+                  hive_posts hp
+                INNER JOIN hive_accounts ha ON ha.id = hp.author_id
+                INNER JOIN hive_permlink_data hpd ON hpd.id = hp.permlink_id
+                WHERE
+                  ha.name = _parent_author AND
+                  hpd.permlink = _parent_permlink
+              )
+            ;
+
             RETURN QUERY INSERT INTO hive_posts as hp
             (parent_id, parent_author_id, parent_permlink_id, depth, community_id,
              category_id,
              root_author_id, root_permlink_id,
              is_muted, is_valid,
-             author_id, permlink_id, created_at, updated_at)
+             author_id, permlink_id, created_at, updated_at, active)
             SELECT php.id AS parent_id, php.author_id as parent_author_id,
                 php.permlink_id as parent_permlink_id, php.depth + 1 as depth,
                 (CASE
@@ -460,7 +484,8 @@ def setup(db):
                 php.root_permlink_id as root_permlink_id,
                 php.is_muted as is_muted, php.is_valid as is_valid,
                 ha.id as author_id, hpd.id as permlink_id, _date as created_at,
-                _date as updated_at
+                _date as updated_at,
+                _date as active
             FROM hive_accounts ha,
                  hive_permlink_data hpd,
                  hive_posts php
@@ -474,6 +499,7 @@ def setup(db):
               --- then also depth, is_valid and is_muted is impossible to change
              --- post edit part
              updated_at = _date,
+             active = _date,
 
               --- post undelete part (if was deleted)
               is_deleted = (CASE hp.is_deleted
@@ -501,7 +527,7 @@ def setup(db):
              category_id,
              root_author_id, root_permlink_id,
              is_muted, is_valid,
-             author_id, permlink_id, created_at, updated_at)
+             author_id, permlink_id, created_at, updated_at, active)
             SELECT 0 AS parent_id, 0 as parent_author_id, 0 as parent_permlink_id, 0 as depth,
                 (CASE
                   WHEN _date > _community_support_start_date THEN
@@ -513,7 +539,8 @@ def setup(db):
                 hpd.id as root_permlink_id, -- use perlink_id as root one if no parent
                 false as is_muted, true as is_valid,
                 ha.id as author_id, hpd.id as permlink_id, _date as created_at,
-                _date as updated_at
+                _date as updated_at,
+                _date as active
             FROM hive_accounts ha,
                  hive_permlink_data hpd
             WHERE ha.name = _author and hpd.permlink = _permlink
@@ -523,6 +550,7 @@ def setup(db):
               --- then also depth, is_valid and is_muted is impossible to change
               --- post edit part
               updated_at = _date,
+              active = _date,
 
               --- post undelete part (if was deleted)
               is_deleted = (CASE hp.is_deleted
@@ -567,25 +595,6 @@ def setup(db):
           """
     db.query_no_return(sql)
 
-    sql = """
-        DROP MATERIALIZED VIEW IF EXISTS hive_posts_a_p
-        ;
-        CREATE MATERIALIZED VIEW hive_posts_a_p
-        AS
-        SELECT hp.id AS id,
-            ha_a.name AS author,
-            hpd_p.permlink AS permlink
-        FROM hive_posts hp
-        INNER JOIN hive_accounts ha_a ON ha_a.id = hp.author_id
-        INNER JOIN hive_permlink_data hpd_p ON hpd_p.id = hp.permlink_id
-        WITH DATA
-        ;
-        DROP INDEX IF EXISTS hive_posts_a_p_idx
-        ;
-        CREATE unique index hive_posts_a_p_idx ON hive_posts_a_p (author collate "C", permlink collate "C")
-    """
-    db.query_no_return(sql)
-
     sql = """
         DROP VIEW IF EXISTS public.hive_posts_view;
 
@@ -595,6 +604,8 @@ def setup(db):
             hp.community_id,
             hp.parent_id,
             ha_a.name AS author,
+            hp.active,
+            hp.author_rewards,
             hp.author_id,
             hpd_p.permlink,
             hpd.title,
@@ -654,7 +665,10 @@ def setup(db):
             hr.title AS role_title, 
             hr.role_id AS role_id,
             hc.title AS community_title,
-            hc.name AS community_name
+            hc.name AS community_name,
+            hp.abs_rshares,
+            hp.max_cashout_time,
+            hp.reward_weight
             FROM hive_posts hp
             JOIN hive_posts rp ON rp.author_id = hp.root_author_id AND rp.permlink_id = hp.root_permlink_id
             JOIN hive_post_data rpd ON rp.id = rpd.id