Prerequisuites to Reputation api support

hive/db/schema.py

@@ -8,6 +8,9 @@ from sqlalchemy.types import VARCHAR
 from sqlalchemy.types import TEXT
 from sqlalchemy.types import BOOLEAN
 
+import logging
+log = logging.getLogger(__name__)
+
 #pylint: disable=line-too-long, too-many-lines, bad-whitespace
 
 # [DK] we changed and removed some tables so i upgraded DB_VERSION to 18
@@ -61,6 +64,23 @@ def build_metadata():
         sa.Index('hive_accounts_ix5', 'cached_at'), # core/listen sweep
     )
 
+
+    sa.Table(
+        'hive_reputation_data', metadata,
+        sa.Column('id', sa.Integer, primary_key=True),
+        sa.Column('author_id', sa.Integer, nullable=False),
+        sa.Column('voter_id', sa.Integer, nullable=False),
+        sa.Column('permlink', sa.String(255, collation='C'), nullable=False),
+        sa.Column('rshares', sa.BigInteger, nullable=False),
+        sa.Column('block_num', sa.Integer,  nullable=False),
+
+#        sa.ForeignKeyConstraint(['voter_id'], ['hive_accounts.id']),
+#        sa.ForeignKeyConstraint(['author_id'], ['hive_accounts.id']),
+#        sa.ForeignKeyConstraint(['block_num'], ['hive_blocks.num']),
+
+        sa.UniqueConstraint('author_id', 'permlink', 'voter_id', name='hive_reputation_data_uk')
+    )
+
     sa.Table(
         'hive_posts', metadata,
         sa.Column('id', sa.Integer, primary_key=True),
@@ -206,9 +226,10 @@ def build_metadata():
         sa.Column('post_id', sa.Integer, nullable=False),
         sa.Column('tag_id', sa.Integer, nullable=False),
         sa.PrimaryKeyConstraint('post_id', 'tag_id', name='hive_post_tags_pk1'),
+
         sa.ForeignKeyConstraint(['post_id'], ['hive_posts.id'], name='hive_post_tags_fk1'),
         sa.ForeignKeyConstraint(['tag_id'], ['hive_tag_data.id'], name='hive_post_tags_fk2'),
-        sa.Index('hive_post_tags_post_id_idx', 'post_id'),
+
         sa.Index('hive_post_tags_tag_id_idx', 'tag_id')
     )
 
@@ -1390,6 +1411,64 @@ def setup(db):
           """
     db.query_no_return(sql)
 
+    sql = """
+          DROP FUNCTION IF EXISTS process_reputation_data(in _block_num hive_blocks.num%TYPE, in _author hive_accounts.name%TYPE,
+            in _permlink hive_permlink_data.permlink%TYPE, in _voter hive_accounts.name%TYPE, in _rshares hive_votes.rshares%TYPE)
+            ;
+
+          CREATE OR REPLACE FUNCTION process_reputation_data(in _block_num hive_blocks.num%TYPE,
+            in _author hive_accounts.name%TYPE, in _permlink hive_permlink_data.permlink%TYPE,
+            in _voter hive_accounts.name%TYPE, in _rshares hive_votes.rshares%TYPE)
+          RETURNS void
+          LANGUAGE sql
+          VOLATILE 
+          AS $BODY$
+            WITH __insert_info AS (
+              INSERT INTO hive_reputation_data
+                (author_id, voter_id, permlink, block_num, rshares)
+              --- Warning DISTINCT is needed here since we have to strict join to hv table and there is really made a CROSS JOIN
+              --- between ha and hv records (producing 2 duplicated records)
+              SELECT DISTINCT ha.id as author_id, hv.id as voter_id, _permlink, _block_num, _rshares
+              FROM hive_accounts ha
+              JOIN hive_accounts hv ON hv.name = _voter
+              JOIN hive_posts hp ON hp.author_id = ha.id
+              JOIN hive_permlink_data hpd ON hp.permlink_id = hpd.id
+              WHERE hpd.permlink = _permlink
+                    AND ha.name = _author
+
+                    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 
+                          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
+                                                                              AND hrd.voter_id=hv.id
+                                                                              AND hrd.permlink=_permlink
+                                                                              AND hrd.rshares < 0), 0)))
+                        )
+              ON CONFLICT ON CONSTRAINT hive_reputation_data_uk DO
+              UPDATE SET
+                rshares = EXCLUDED.rshares
+              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) !!!
+                        WHERE hrd.id = hive_reputation_data.id AND hrd.author_id = author_id and hrd.voter_id=voter_id and hrd.permlink=_permlink) AS old_rshares, author_id, voter_id
+            )
+          UPDATE hive_accounts uha
+            SET reputation = CASE __insert_info.is_new_vote
+                               WHEN true THEN ha.reputation + (_rshares >> 6)
+                               ELSE ha.reputation - (__insert_info.old_rshares >> 6) + (_rshares >> 6)
+                             END
+            FROM hive_accounts ha
+            JOIN __insert_info ON ha.id = __insert_info.author_id
+            WHERE uha.id = __insert_info.author_id
+            ;
+          $BODY$;
+    """
+
+    db.query_no_return(sql)
+
 def reset_autovac(db):
     """Initializes/resets per-table autovacuum/autoanalyze params.
 
@@ -1421,9 +1500,28 @@ def set_fillfactor(db):
     fillfactor_config = {
         'hive_posts': 70,
         'hive_post_data': 70,
-        'hive_votes': 70
+        'hive_votes': 70,
+        'hive_reputation_data': 50
     }
 
     for table, fillfactor in fillfactor_config.items():
         sql = """ALTER TABLE {} SET (FILLFACTOR = {})"""
         db.query(sql.format(table, fillfactor))
+
+def set_logged_table_attribute(db, logged):
+    """Initializes/resets LOGGED/UNLOGGED attribute for tables which are intesively updated"""
+
+    logged_config = [
+        'hive_accounts',
+        'hive_permlink_data',
+        'hive_post_tags',
+        'hive_posts',
+        'hive_post_data',
+        'hive_votes',
+        'hive_reputation_data'
+    ]
+
+    for table in logged_config:
+        log.info("Setting {} attribute on a table: {}".format('LOGGED' if logged else 'UNLOGGED', table))
+        sql = """ALTER TABLE {} SET {}"""
+        db.query_no_return(sql.format(table, 'LOGGED' if logged else 'UNLOGGED'))