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

-"""Db schema definitions and setup routines."""
-
-import sqlalchemy as sa
-from sqlalchemy.sql import text as sql_text
-from sqlalchemy.types import SMALLINT
-from sqlalchemy.types import CHAR
-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
-DB_VERSION = 18
-
-def build_metadata():
-    """Build schema def with SqlAlchemy"""
-    metadata = sa.MetaData()
-
-    sa.Table(
-        'hive_blocks', metadata,
-        sa.Column('num', sa.Integer, primary_key=True, autoincrement=False),
-        sa.Column('hash', CHAR(40), nullable=False),
-        sa.Column('prev', CHAR(40)),
-        sa.Column('txs', SMALLINT, server_default='0', nullable=False),
-        sa.Column('ops', SMALLINT, server_default='0', nullable=False),
-        sa.Column('created_at', sa.DateTime, nullable=False),
-
-        sa.UniqueConstraint('hash', name='hive_blocks_ux1'),
-        sa.ForeignKeyConstraint(['prev'], ['hive_blocks.hash'], name='hive_blocks_fk1'),
-    )
-
-    sa.Table(
-        'hive_accounts', metadata,
-        sa.Column('id', sa.Integer, primary_key=True),
-        sa.Column('name', VARCHAR(16, collation='C'), nullable=False),
-        sa.Column('created_at', sa.DateTime, nullable=False),
-        #sa.Column('block_num', sa.Integer, nullable=False),
-        sa.Column('reputation', sa.Float(precision=6), nullable=False, server_default='25'),
-
-        sa.Column('display_name', sa.String(20)),
-        sa.Column('about', sa.String(160)),
-        sa.Column('location', sa.String(30)),
-        sa.Column('website', sa.String(1024)),
-        sa.Column('profile_image', sa.String(1024), nullable=False, server_default=''),
-        sa.Column('cover_image', sa.String(1024), nullable=False, server_default=''),
-
-        sa.Column('followers', sa.Integer, nullable=False, server_default='0'),
-        sa.Column('following', sa.Integer, nullable=False, server_default='0'),
-
-        sa.Column('proxy', VARCHAR(16), nullable=False, server_default=''),
-        sa.Column('proxy_weight', sa.Float(precision=6), nullable=False, server_default='0'),
-        sa.Column('kb_used', sa.Integer, nullable=False, server_default='0'), # deprecated
-        sa.Column('rank', sa.Integer, nullable=False, server_default='0'),
-
-        sa.Column('lastread_at', sa.DateTime, nullable=False, server_default='1970-01-01 00:00:00'),
-        sa.Column('cached_at', sa.DateTime, nullable=False, server_default='1970-01-01 00:00:00'),
-        sa.Column('raw_json', sa.Text),
-
-        sa.UniqueConstraint('name', name='hive_accounts_ux1'),
-        sa.Index('hive_accounts_ix5', 'cached_at'), # core/listen sweep
-	sa.Index('hive_accounts_ix6', 'reputation')
-    )
-
-
-    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),
-        sa.Column('root_id', sa.Integer, nullable=False), # records having initially set 0 will be updated to their id
-        sa.Column('parent_id', sa.Integer, nullable=False),
-        sa.Column('author_id', sa.Integer, nullable=False),
-        sa.Column('permlink_id', sa.Integer, nullable=False),
-        sa.Column('category_id', sa.Integer, nullable=False),
-        sa.Column('community_id', sa.Integer, nullable=True),
-        sa.Column('created_at', sa.DateTime, nullable=False),
-        sa.Column('depth', SMALLINT, nullable=False),
-        sa.Column('counter_deleted', sa.Integer, nullable=False, server_default='0'),
-        sa.Column('is_pinned', BOOLEAN, nullable=False, server_default='0'),
-        sa.Column('is_muted', BOOLEAN, nullable=False, server_default='0'),
-        sa.Column('is_valid', BOOLEAN, nullable=False, server_default='1'),
-        sa.Column('promoted', sa.types.DECIMAL(10, 3), nullable=False, server_default='0'),
-
-        sa.Column('children', sa.Integer, nullable=False, server_default='0'),
-
-
-        # core stats/indexes
-        sa.Column('payout', sa.types.DECIMAL(10, 3), nullable=False, server_default='0'),
-        sa.Column('pending_payout', sa.types.DECIMAL(10, 3), nullable=False, server_default='0'),
-        sa.Column('payout_at', sa.DateTime, nullable=False, server_default='1970-01-01'),
-        sa.Column('last_payout_at', sa.DateTime, nullable=False, server_default='1970-01-01'),
-        sa.Column('updated_at', sa.DateTime, nullable=False, server_default='1970-01-01'),
-        sa.Column('is_paidout', BOOLEAN, nullable=False, server_default='0'),
-
-        # ui flags/filters
-        sa.Column('is_nsfw', BOOLEAN, nullable=False, server_default='0'),
-        sa.Column('is_declined', BOOLEAN, nullable=False, server_default='0'),
-        sa.Column('is_full_power', BOOLEAN, nullable=False, server_default='0'),
-        sa.Column('is_hidden', BOOLEAN, nullable=False, server_default='0'),
-        sa.Column('is_grayed', BOOLEAN, nullable=False, server_default='0'),
-
-        # important indexes
-        sa.Column('sc_trend', sa.Float(precision=6), nullable=False, server_default='0'),
-        sa.Column('sc_hot', sa.Float(precision=6), nullable=False, server_default='0'),
-
-        sa.Column('total_payout_value', sa.String(30), nullable=False, server_default='0.000 HBD'),
-        sa.Column('author_rewards', sa.BigInteger, nullable=False, server_default='0'),
-
-        sa.Column('author_rewards_hive', sa.BigInteger, nullable=False, server_default='0'),
-        sa.Column('author_rewards_hbd', sa.BigInteger, nullable=False, server_default='0'),
-        sa.Column('author_rewards_vests', sa.BigInteger, nullable=False, server_default='0'),
-
-        sa.Column('abs_rshares', sa.BigInteger, nullable=False, server_default='0'),
-        sa.Column('vote_rshares', sa.BigInteger, nullable=False, server_default='0'),
-        sa.Column('total_vote_weight', sa.Numeric, nullable=False, server_default='0'),
-        sa.Column('active', 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('percent_hbd', sa.Integer, nullable=False, server_default='10000'),
-
-        sa.Column('curator_payout_value', sa.String(30), nullable=False, server_default='0.000 HBD'),
-        sa.Column('max_accepted_payout',  sa.String(30), nullable=False, server_default='1000000.000 HBD'),
-        sa.Column('allow_votes', BOOLEAN, nullable=False, server_default='1'),
-        sa.Column('allow_curation_rewards', BOOLEAN, nullable=False, server_default='1'),
-        sa.Column('beneficiaries', sa.JSON, nullable=False, server_default='[]'),
-        sa.Column('block_num', sa.Integer,  nullable=False ),
-
-        sa.ForeignKeyConstraint(['author_id'], ['hive_accounts.id'], name='hive_posts_fk1'),
-        sa.ForeignKeyConstraint(['root_id'], ['hive_posts.id'], name='hive_posts_fk2'),
-        sa.ForeignKeyConstraint(['parent_id'], ['hive_posts.id'], name='hive_posts_fk3'),
-        sa.UniqueConstraint('author_id', 'permlink_id', 'counter_deleted', name='hive_posts_ux1'),
-
-        sa.Index('hive_posts_depth_idx', 'depth'),
-
-        sa.Index('hive_posts_root_id_id_idx', 'root_id','id'),
-
-        sa.Index('hive_posts_parent_id_idx', 'parent_id'),
-        sa.Index('hive_posts_community_id_idx', 'community_id'),
-
-        sa.Index('hive_posts_category_id_idx', 'category_id'),
-        sa.Index('hive_posts_payout_at_idx', 'payout_at'),
-        sa.Index('hive_posts_payout_idx', 'payout'),
-        sa.Index('hive_posts_promoted_idx', 'promoted'),
-        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_block_num_idx', 'block_num')
-    )
-
-    sa.Table(
-        'hive_post_data', metadata,
-        sa.Column('id', sa.Integer, primary_key=True, autoincrement=False),
-        sa.Column('title', VARCHAR(512), nullable=False, server_default=''),
-        sa.Column('preview', VARCHAR(1024), nullable=False, server_default=''), # first 1k of 'body'
-        sa.Column('img_url', VARCHAR(1024), nullable=False, server_default=''), # first 'image' from 'json'
-        sa.Column('body', TEXT, nullable=False, server_default=''),
-        sa.Column('json', TEXT, nullable=False, server_default='')
-    )
-
-    sa.Table(
-        'hive_permlink_data', metadata,
-        sa.Column('id', sa.Integer, primary_key=True),
-        sa.Column('permlink', sa.String(255, collation='C'), nullable=False),
-        sa.UniqueConstraint('permlink', name='hive_permlink_data_permlink')
-    )
-
-    sa.Table(
-        'hive_category_data', metadata,
-        sa.Column('id', sa.Integer, primary_key=True),
-        sa.Column('category', sa.String(255, collation='C'), nullable=False),
-        sa.UniqueConstraint('category', name='hive_category_data_category')
-    )
-
-    sa.Table(
-        'hive_votes', metadata,
-        sa.Column('id', sa.BigInteger, primary_key=True),
-        sa.Column('post_id', sa.Integer, nullable=False),
-        sa.Column('voter_id', sa.Integer, nullable=False),
-        sa.Column('author_id', sa.Integer, nullable=False),
-        sa.Column('permlink_id', sa.Integer, nullable=False),
-        sa.Column('weight', sa.Numeric, nullable=False, server_default='0'),
-        sa.Column('rshares', sa.BigInteger, nullable=False, server_default='0'),
-        sa.Column('vote_percent', sa.Integer, server_default='0'),
-        sa.Column('last_update', sa.DateTime, nullable=False, server_default='1970-01-01 00:00:00'),
-        sa.Column('num_changes', sa.Integer, server_default='0'),
-        sa.Column('block_num', sa.Integer,  nullable=False ),
-        sa.Column('is_effective', BOOLEAN, nullable=False, server_default='0'),
-
-        sa.UniqueConstraint('voter_id', 'author_id', 'permlink_id', name='hive_votes_ux1'),
-
-        sa.ForeignKeyConstraint(['post_id'], ['hive_posts.id'], name='hive_votes_fk1'),
-        sa.ForeignKeyConstraint(['voter_id'], ['hive_accounts.id'], name='hive_votes_fk2'),
-        sa.ForeignKeyConstraint(['author_id'], ['hive_accounts.id'], name='hive_votes_fk3'),
-        sa.ForeignKeyConstraint(['permlink_id'], ['hive_permlink_data.id'], name='hive_votes_fk4'),
-        sa.ForeignKeyConstraint(['block_num'], ['hive_blocks.num'], name='hive_votes_fk5'),
-
-        sa.Index('hive_votes_post_id_idx', 'post_id'),
-        sa.Index('hive_votes_voter_id_idx', 'voter_id'),
-        sa.Index('hive_votes_voter_id_post_id_idx', 'voter_id', 'post_id'),
-        sa.Index('hive_votes_post_id_voter_id_idx', 'post_id', 'voter_id'),
-        sa.Index('hive_votes_block_num_idx', 'block_num')
-    )
-
-    sa.Table(
-        'hive_tag_data', metadata,
-        sa.Column('id', sa.Integer, nullable=False, primary_key=True),
-        sa.Column('tag', VARCHAR(64, collation='C'), nullable=False, server_default=''),
-        sa.UniqueConstraint('tag', name='hive_tag_data_ux1')
-    )
-
-    sa.Table(
-        'hive_post_tags', 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_tag_id_idx', 'tag_id')
-    )
-
-    sa.Table(
-        'hive_follows', metadata,
-        sa.Column('id', sa.Integer, primary_key=True ),
-        sa.Column('follower', sa.Integer, nullable=False),
-        sa.Column('following', sa.Integer, nullable=False),
-        sa.Column('state', SMALLINT, nullable=False, server_default='1'),
-        sa.Column('created_at', sa.DateTime, nullable=False),
-        sa.Column('blacklisted', sa.Boolean, nullable=False, server_default='0'),
-        sa.Column('follow_blacklists', sa.Boolean, nullable=False, server_default='0'),
-        sa.Column('block_num', sa.Integer,  nullable=False ),
-
-        sa.UniqueConstraint('following', 'follower', name='hive_follows_ux1'), # core
-        sa.ForeignKeyConstraint(['block_num'], ['hive_blocks.num'], name='hive_follows_fk1'),
-        sa.Index('hive_follows_ix5a', 'following', 'state', 'created_at', 'follower'),
-        sa.Index('hive_follows_ix5b', 'follower', 'state', 'created_at', 'following'),
-        sa.Index('hive_follows_block_num_idx', 'block_num')
-    )
-
-    sa.Table(
-        'hive_reblogs', metadata,
-        sa.Column('id', sa.Integer, primary_key=True ),
-        sa.Column('account', VARCHAR(16), nullable=False),
-        sa.Column('post_id', sa.Integer, nullable=False),
-        sa.Column('created_at', sa.DateTime, nullable=False),
-        sa.Column('block_num', sa.Integer,  nullable=False ),
-
-        sa.ForeignKeyConstraint(['account'], ['hive_accounts.name'], name='hive_reblogs_fk1'),
-        sa.ForeignKeyConstraint(['post_id'], ['hive_posts.id'], name='hive_reblogs_fk2'),
-        sa.ForeignKeyConstraint(['block_num'], ['hive_blocks.num'], name='hive_reblogs_fk3'),
-        sa.UniqueConstraint('account', 'post_id', name='hive_reblogs_ux1'), # core
-        sa.Index('hive_reblogs_account', 'account'),
-        sa.Index('hive_reblogs_post_id', 'post_id'),
-        sa.Index('hive_reblogs_block_num_idx', 'block_num')
-    )
-
-    sa.Table(
-        'hive_payments', metadata,
-        sa.Column('id', sa.Integer, primary_key=True),
-        sa.Column('block_num', sa.Integer, nullable=False),
-        sa.Column('tx_idx', SMALLINT, nullable=False),
-        sa.Column('post_id', sa.Integer, nullable=False),
-        sa.Column('from_account', sa.Integer, nullable=False),
-        sa.Column('to_account', sa.Integer, nullable=False),
-        sa.Column('amount', sa.types.DECIMAL(10, 3), nullable=False),
-        sa.Column('token', VARCHAR(5), nullable=False),
-
-        sa.ForeignKeyConstraint(['from_account'], ['hive_accounts.id'], name='hive_payments_fk1'),
-        sa.ForeignKeyConstraint(['to_account'], ['hive_accounts.id'], name='hive_payments_fk2'),
-        sa.ForeignKeyConstraint(['post_id'], ['hive_posts.id'], name='hive_payments_fk3'),
-        sa.Index('hive_payments_from', 'from_account'),
-        sa.Index('hive_payments_to', 'to_account'),
-        sa.Index('hive_payments_post_id', 'post_id'),
-    )
-
-    sa.Table(
-        'hive_feed_cache', metadata,
-        sa.Column('post_id', sa.Integer, nullable=False, primary_key=True),
-        sa.Column('account_id', sa.Integer, nullable=False),
-        sa.Column('created_at', sa.DateTime, nullable=False),
-        sa.Index('hive_feed_cache_account_id', 'account_id'), # API (and rebuild?)
-        sa.UniqueConstraint('account_id', 'post_id', name='hive_feed_cache_ux1')
-    )
-
-    sa.Table(
-        'hive_state', metadata,
-        sa.Column('block_num', sa.Integer, primary_key=True, autoincrement=False),
-        sa.Column('db_version', sa.Integer, nullable=False),
-        sa.Column('steem_per_mvest', sa.types.DECIMAL(14, 6), nullable=False),
-        sa.Column('usd_per_steem', sa.types.DECIMAL(14, 6), nullable=False),
-        sa.Column('sbd_per_steem', sa.types.DECIMAL(14, 6), nullable=False),
-        sa.Column('dgpo', sa.Text, nullable=False),
-    )
-
-    sa.Table(
-        'hive_posts_api_helper', metadata,
-        sa.Column('id', sa.Integer, primary_key=True, autoincrement = False),
-        sa.Column('author', VARCHAR(16, collation='C'), nullable=False),
-        sa.Column('parent_author', VARCHAR(16, collation='C'), nullable=False),
-        sa.Column('parent_permlink_or_category', sa.String(255, collation='C'), nullable=False),
-        sa.Index('hive_posts_api_helper_parent_permlink_or_category', 'parent_author', 'parent_permlink_or_category', 'id')
-    )
-
-    metadata = build_metadata_community(metadata)
-
-    return metadata
-
-def build_metadata_community(metadata=None):
-    """Build community schema defs"""
-    if not metadata:
-        metadata = sa.MetaData()
-
-    sa.Table(
-        'hive_communities', metadata,
-        sa.Column('id',          sa.Integer,      primary_key=True, autoincrement=False),
-        sa.Column('type_id',     SMALLINT,        nullable=False),
-        sa.Column('lang',        CHAR(2),         nullable=False, server_default='en'),
-        sa.Column('name',        VARCHAR(16, collation='C'), nullable=False),
-        sa.Column('title',       sa.String(32),   nullable=False, server_default=''),
-        sa.Column('created_at',  sa.DateTime,     nullable=False),
-        sa.Column('sum_pending', sa.Integer,      nullable=False, server_default='0'),
-        sa.Column('num_pending', sa.Integer,      nullable=False, server_default='0'),
-        sa.Column('num_authors', sa.Integer,      nullable=False, server_default='0'),
-        sa.Column('rank',        sa.Integer,      nullable=False, server_default='0'),
-        sa.Column('subscribers', sa.Integer,      nullable=False, server_default='0'),
-        sa.Column('is_nsfw',     BOOLEAN,         nullable=False, server_default='0'),
-        sa.Column('about',       sa.String(120),  nullable=False, server_default=''),
-        sa.Column('primary_tag', sa.String(32),   nullable=False, server_default=''),
-        sa.Column('category',    sa.String(32),   nullable=False, server_default=''),
-        sa.Column('avatar_url',  sa.String(1024), nullable=False, server_default=''),
-        sa.Column('description', sa.String(5000), nullable=False, server_default=''),
-        sa.Column('flag_text',   sa.String(5000), nullable=False, server_default=''),
-        sa.Column('settings',    TEXT,            nullable=False, server_default='{}'),
-        sa.Column('block_num', sa.Integer,  nullable=False ),
-
-        sa.UniqueConstraint('name', name='hive_communities_ux1'),
-        sa.Index('hive_communities_ix1', 'rank', 'id'),
-        sa.Index('hive_communities_block_num_idx', 'block_num')
-    )
-
-    sa.Table(
-        'hive_roles', metadata,
-        sa.Column('account_id',   sa.Integer,     nullable=False),
-        sa.Column('community_id', sa.Integer,     nullable=False),
-        sa.Column('created_at',   sa.DateTime,    nullable=False),
-        sa.Column('role_id',      SMALLINT,       nullable=False, server_default='0'),
-        sa.Column('title',        sa.String(140), nullable=False, server_default=''),
-
-        sa.PrimaryKeyConstraint('account_id', 'community_id', name='hive_roles_pk'),
-        sa.Index('hive_roles_ix1', 'community_id', 'account_id', 'role_id'),
-    )
-
-    sa.Table(
-        'hive_subscriptions', metadata,
-        sa.Column('id', sa.Integer, primary_key=True),
-        sa.Column('account_id',   sa.Integer,  nullable=False),
-        sa.Column('community_id', sa.Integer,  nullable=False),
-        sa.Column('created_at',   sa.DateTime, nullable=False),
-        sa.Column('block_num', sa.Integer,  nullable=False ),
-
-        sa.UniqueConstraint('account_id', 'community_id', name='hive_subscriptions_ux1'),
-        sa.Index('hive_subscriptions_ix1', 'community_id', 'account_id', 'created_at'),
-        sa.Index('hive_subscriptions_block_num_idx', 'block_num')
-    )
-
-    sa.Table(
-        'hive_notifs', metadata,
-        sa.Column('id',           sa.Integer,  primary_key=True),
-        sa.Column('type_id',      SMALLINT,    nullable=False),
-        sa.Column('score',        SMALLINT,    nullable=False),
-        sa.Column('created_at',   sa.DateTime, nullable=False),
-        sa.Column('src_id',       sa.Integer,  nullable=True),
-        sa.Column('dst_id',       sa.Integer,  nullable=True),
-        sa.Column('post_id',      sa.Integer,  nullable=True),
-        sa.Column('community_id', sa.Integer,  nullable=True),
-        sa.Column('block_num',    sa.Integer,  nullable=True),
-        sa.Column('payload',      sa.Text,     nullable=True),
-
-        sa.Index('hive_notifs_ix1', 'dst_id',                  'id', postgresql_where=sql_text("dst_id IS NOT NULL")),
-        sa.Index('hive_notifs_ix2', 'community_id',            'id', postgresql_where=sql_text("community_id IS NOT NULL")),
-        sa.Index('hive_notifs_ix3', 'community_id', 'type_id', 'id', postgresql_where=sql_text("community_id IS NOT NULL")),
-        sa.Index('hive_notifs_ix4', 'community_id', 'post_id', 'type_id', 'id', postgresql_where=sql_text("community_id IS NOT NULL AND post_id IS NOT NULL")),
-        sa.Index('hive_notifs_ix5', 'post_id', 'type_id', 'dst_id', 'src_id', postgresql_where=sql_text("post_id IS NOT NULL AND type_id IN (16,17)")), # filter: dedupe
-        sa.Index('hive_notifs_ix6', 'dst_id', 'created_at', 'score', 'id', postgresql_where=sql_text("dst_id IS NOT NULL")), # unread
-    )
-
-    return metadata
-
-
-def teardown(db):
-    """Drop all tables"""
-    build_metadata().drop_all(db.engine())
-
-def drop_fk(db):
-    db.query_no_return("START TRANSACTION")
-    for table in build_metadata().sorted_tables:
-        for fk in table.foreign_keys:
-            sql = """ALTER TABLE {} DROP CONSTRAINT IF EXISTS {}""".format(table.name, fk.name)
-            db.query_no_return(sql)
-    db.query_no_return("COMMIT")
-
-def create_fk(db):
-    from sqlalchemy.schema import AddConstraint
-    from sqlalchemy import text
-    connection = db.engine().connect()
-    connection.execute(text("START TRANSACTION"))
-    for table in build_metadata().sorted_tables:
-        for fk in table.foreign_keys:
-            connection.execute(AddConstraint(fk.constraint))
-    connection.execute(text("COMMIT"))
-
-def setup(db):
-    """Creates all tables and seed data"""
-    # initialize schema
-    build_metadata().create_all(db.engine())
-
-    # tune auto vacuum/analyze
-    reset_autovac(db)
-
-    # sets FILLFACTOR:
-    set_fillfactor(db)
-
-    # default rows
-    sqls = [
-        "INSERT INTO hive_state (block_num, db_version, steem_per_mvest, usd_per_steem, sbd_per_steem, dgpo) VALUES (0, %d, 0, 0, 0, '')" % DB_VERSION,
-        "INSERT INTO hive_blocks (num, hash, created_at) VALUES (0, '0000000000000000000000000000000000000000', '2016-03-24 16:04:57')",
-
-        "INSERT INTO hive_permlink_data (id, permlink) VALUES (0, '')",
-        "INSERT INTO hive_category_data (id, category) VALUES (0, '')",
-        "INSERT INTO hive_accounts (id, name, created_at) VALUES (0, '', '1970-01-01T00:00:00')",
-
-        "INSERT INTO hive_accounts (name, created_at) VALUES ('miners',    '2016-03-24 16:05:00')",
-        "INSERT INTO hive_accounts (name, created_at) VALUES ('null',      '2016-03-24 16:05:00')",
-        "INSERT INTO hive_accounts (name, created_at) VALUES ('temp',      '2016-03-24 16:05:00')",
-        "INSERT INTO hive_accounts (name, created_at) VALUES ('initminer', '2016-03-24 16:05:00')",
-
-        """
-        INSERT INTO
-            public.hive_posts(id, root_id, parent_id, author_id, permlink_id, category_id,
-                community_id, created_at, depth, block_num
-            )
-        VALUES
-            (0, 0, 0, 0, 0, 0, 0, now(), 0, 0);
-        """]
-    for sql in sqls:
-        db.query(sql)
-
-    sql = "CREATE INDEX hive_communities_ft1 ON hive_communities USING GIN (to_tsvector('english', title || ' ' || about))"
-    db.query(sql)
-
-    sql = """
-          DROP FUNCTION if exists process_hive_post_operation(character varying,character varying,character varying,character varying,timestamp without time zone,timestamp without time zone)
-          ;
-          CREATE OR REPLACE FUNCTION process_hive_post_operation(
-            in _author hive_accounts.name%TYPE,
-            in _permlink hive_permlink_data.permlink%TYPE,
-            in _parent_author hive_accounts.name%TYPE,
-            in _parent_permlink hive_permlink_data.permlink%TYPE,
-            in _date hive_posts.created_at%TYPE,
-            in _community_support_start_date hive_posts.created_at%TYPE,
-            in _block_num hive_posts.block_num%TYPE)
-          RETURNS TABLE (is_new_post boolean, id hive_posts.id%TYPE, author_id hive_posts.author_id%TYPE, permlink_id hive_posts.permlink_id%TYPE,
-                         post_category hive_category_data.category%TYPE, parent_id hive_posts.parent_id%TYPE, community_id hive_posts.community_id%TYPE,
-                         is_valid hive_posts.is_valid%TYPE, is_muted hive_posts.is_muted%TYPE, depth hive_posts.depth%TYPE)
-          LANGUAGE plpgsql
-          AS
-          $function$
-          BEGIN
-
-          INSERT INTO hive_permlink_data
-          (permlink)
-          values
-          (
-          _permlink
-          )
-          ON CONFLICT DO NOTHING
-          ;
-          if _parent_author != '' THEN
-            RETURN QUERY INSERT INTO hive_posts as hp
-            (parent_id, depth, community_id, category_id,
-             root_id, is_muted, is_valid,
-             author_id, permlink_id, created_at, updated_at, sc_hot, sc_trend, active, payout_at, cashout_time, counter_deleted, block_num)
-            SELECT php.id AS parent_id, php.depth + 1 AS depth,
-                (CASE
-                   WHEN _date > _community_support_start_date THEN
-                     COALESCE(php.community_id, (select hc.id from hive_communities hc where hc.name = _parent_permlink))
-                   ELSE NULL
-                END) AS community_id,
-                COALESCE(php.category_id, (select hcg.id from hive_category_data hcg where hcg.category = _parent_permlink)) AS category_id,
-                (CASE(php.root_id)
-                   WHEN 0 THEN php.id
-                   ELSE php.root_id
-                 END) AS root_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,
-                calculate_time_part_of_hot(_date) AS sc_hot,
-                calculate_time_part_of_trending(_date) AS sc_trend,
-                _date AS active, (_date + INTERVAL '7 days') AS payout_at, (_date + INTERVAL '7 days') AS cashout_time, 0, _block_num as block_num
-            FROM hive_accounts ha,
-                 hive_permlink_data hpd,
-                 hive_posts php
-            INNER JOIN hive_accounts pha ON pha.id = php.author_id
-            INNER JOIN hive_permlink_data phpd ON phpd.id = php.permlink_id
-            WHERE pha.name = _parent_author AND phpd.permlink = _parent_permlink AND
-                   ha.name = _author AND hpd.permlink = _permlink AND php.counter_deleted = 0
-
-            ON CONFLICT ON CONSTRAINT hive_posts_ux1 DO UPDATE SET
-              --- During post update it is disallowed to change: parent-post, category, community-id
-              --- then also depth, is_valid and is_muted is impossible to change
-             --- post edit part
-             updated_at = _date,
-             active = _date
-            RETURNING (xmax = 0) as is_new_post, hp.id, hp.author_id, hp.permlink_id, (SELECT hcd.category FROM hive_category_data hcd WHERE hcd.id = hp.category_id) as post_category, hp.parent_id, hp.community_id, hp.is_valid, hp.is_muted, hp.depth
-          ;
-          ELSE
-            INSERT INTO hive_category_data
-            (category)
-            VALUES (_parent_permlink)
-            ON CONFLICT (category) DO NOTHING
-            ;
-
-            RETURN QUERY INSERT INTO hive_posts as hp
-            (parent_id, depth, community_id, category_id,
-             root_id, is_muted, is_valid,
-             author_id, permlink_id, created_at, updated_at, sc_hot, sc_trend, active, payout_at, cashout_time, counter_deleted, block_num)
-            SELECT 0 AS parent_id, 0 AS depth,
-                (CASE
-                  WHEN _date > _community_support_start_date THEN
-                    (select hc.id FROM hive_communities hc WHERE hc.name = _parent_permlink)
-                  ELSE NULL
-                END) AS community_id,
-                (SELECT hcg.id FROM hive_category_data hcg WHERE hcg.category = _parent_permlink) AS category_id,
-                0 as root_id, -- will use 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,
-                calculate_time_part_of_hot(_date) AS sc_hot,
-                calculate_time_part_of_trending(_date) AS sc_trend,
-                _date AS active, (_date + INTERVAL '7 days') AS payout_at, (_date + INTERVAL '7 days') AS cashout_time, 0, _block_num as block_num
-            FROM hive_accounts ha,
-                 hive_permlink_data hpd
-            WHERE ha.name = _author and hpd.permlink = _permlink
-
-            ON CONFLICT ON CONSTRAINT hive_posts_ux1 DO UPDATE SET
-              --- During post update it is disallowed to change: parent-post, category, community-id
-              --- then also depth, is_valid and is_muted is impossible to change
-              --- post edit part
-              updated_at = _date,
-              active = _date,
-              block_num = _block_num
-
-            RETURNING (xmax = 0) as is_new_post, hp.id, hp.author_id, hp.permlink_id, _parent_permlink as post_category, hp.parent_id, hp.community_id, hp.is_valid, hp.is_muted, hp.depth
-            ;
-          END IF;
-          END
-          $function$
-    """
-    db.query_no_return(sql)
-
-    sql = """
-          DROP FUNCTION if exists delete_hive_post(character varying,character varying,character varying)
-          ;
-          CREATE OR REPLACE FUNCTION delete_hive_post(
-            in _author hive_accounts.name%TYPE,
-            in _permlink hive_permlink_data.permlink%TYPE)
-          RETURNS TABLE (id hive_posts.id%TYPE, depth hive_posts.depth%TYPE)
-          LANGUAGE plpgsql
-          AS
-          $function$
-          BEGIN
-            RETURN QUERY UPDATE hive_posts AS hp
-              SET counter_deleted =
-              (
-                SELECT max( hps.counter_deleted ) + 1
-                FROM hive_posts hps
-                INNER JOIN hive_accounts ha ON hps.author_id = ha.id
-                INNER JOIN hive_permlink_data hpd ON hps.permlink_id = hpd.id
-                WHERE ha.name = _author AND hpd.permlink = _permlink
-              )
-            FROM hive_posts hp1
-            INNER JOIN hive_accounts ha ON hp1.author_id = ha.id
-            INNER JOIN hive_permlink_data hpd ON hp1.permlink_id = hpd.id
-            WHERE hp.id = hp1.id AND ha.name = _author AND hpd.permlink = _permlink AND hp1.counter_deleted = 0
-            RETURNING hp.id, hp.depth;
-          END
-          $function$
-          """
-    db.query_no_return(sql)
-
-    # In original hivemind, a value of 'active_at' was calculated from
-    # max
-    #   {
-    #     created             ( account_create_operation ),
-    #     last_account_update ( account_update_operation/account_update2_operation ),
-    #     last_post           ( comment_operation - only creation )
-    #     last_root_post      ( comment_operation - only creation + only ROOT ),
-    #     last_vote_time      ( vote_operation )
-    #   }
-    # In order to simplify calculations, `last_account_update` is not taken into consideration, because this updating accounts is very rare
-    # and posting/voting after an account updating, fixes `active_at` value immediately.
-
-    sql = """
-        DROP VIEW IF EXISTS public.hive_accounts_info_view;
-
-        CREATE OR REPLACE VIEW public.hive_accounts_info_view
-        AS
-        SELECT
-          id,
-          name,
-          (
-            select count(*) post_count
-            FROM hive_posts hp
-            WHERE ha.id=hp.author_id
-          ) 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'
-		          )
-          	)
-          ) active_at,
-          display_name,
-          about,
-          reputation,
-          profile_image,
-          location,
-          website,
-          cover_image,
-          rank,
-          following,
-          followers,
-          proxy,
-          proxy_weight,
-          lastread_at,
-          cached_at,
-          raw_json
-        FROM
-          hive_accounts ha
-          """
-    db.query_no_return(sql)
-
-    sql = """
-        DROP VIEW IF EXISTS public.hive_posts_view;
-
-        CREATE OR REPLACE VIEW public.hive_posts_view
-        AS
-        SELECT hp.id,
-          hp.community_id,
-          hp.root_id,
-          hp.parent_id,
-          ha_a.name AS author,
-          hp.active,
-          hp.author_rewards,
-          hp.author_id,
-          hpd_p.permlink,
-          hpd.title,
-          hpd.body,
-          hpd.img_url,
-          hpd.preview,
-          hcd.category,
-          hp.depth,
-          hp.promoted,
-          hp.payout,
-          hp.pending_payout,
-          hp.payout_at,
-          hp.last_payout_at,
-          hp.cashout_time,
-          hp.is_paidout,
-          hp.children,
-          0 AS votes,
-          0 AS active_votes,
-          hp.created_at,
-          hp.updated_at,
-            COALESCE(
-              (
-                SELECT SUM( v.rshares )
-                FROM hive_votes v
-                WHERE v.post_id = hp.id
-                GROUP BY v.post_id
-              ), 0
-            ) AS rshares,
-            COALESCE(
-              (
-                SELECT SUM( CASE v.rshares >= 0 WHEN True THEN v.rshares ELSE -v.rshares END )
-                FROM hive_votes v
-                WHERE v.post_id = hp.id AND NOT v.rshares = 0
-                GROUP BY v.post_id
-              ), 0
-            ) AS abs_rshares,
-            COALESCE(
-              (
-                SELECT COUNT( 1 )
-                FROM hive_votes v
-                WHERE v.post_id = hp.id AND v.is_effective
-                GROUP BY v.post_id
-              ), 0
-            ) AS total_votes,
-            COALESCE(
-              (
-                SELECT SUM( CASE v.rshares > 0 WHEN True THEN 1 ELSE -1 END )
-                FROM hive_votes v
-                WHERE v.post_id = hp.id AND NOT v.rshares = 0
-                GROUP BY v.post_id
-              ), 0
-            ) AS net_votes,
-          hpd.json,
-          ha_a.reputation AS author_rep,
-          hp.is_hidden,
-          hp.is_grayed,
-          hp.total_vote_weight,
-          ha_pp.name AS parent_author,
-          ha_pp.id AS parent_author_id,
-            ( CASE hp.depth > 0
-              WHEN True THEN hpd_pp.permlink
-              ELSE hcd.category
-            END ) AS parent_permlink_or_category,
-          hp.curator_payout_value,
-          ha_rp.name AS root_author,
-          hpd_rp.permlink AS root_permlink,
-          rcd.category as root_category,
-          hp.max_accepted_payout,
-          hp.percent_hbd,
-            True AS allow_replies,
-          hp.allow_votes,
-          hp.allow_curation_rewards,
-          hp.beneficiaries,
-            CONCAT('/', rcd.category, '/@', ha_rp.name, '/', hpd_rp.permlink,
-              CASE (rp.id)
-                WHEN hp.id THEN ''
-                ELSE CONCAT('#@', ha_a.name, '/', hpd_p.permlink)
-              END
-            ) AS url,
-          rpd.title AS root_title,
-          hp.sc_trend,
-          hp.sc_hot,
-          hp.is_pinned,
-          hp.is_muted,
-          hp.is_nsfw,
-          hp.is_valid,
-          hr.title AS role_title,
-          hr.role_id AS role_id,
-          hc.title AS community_title,
-          hc.name AS community_name,
-          hp.block_num
-          FROM hive_posts hp
-            JOIN hive_posts pp ON pp.id = hp.parent_id
-            JOIN hive_posts rp ON rp.id = hp.root_id
-            JOIN hive_accounts ha_a ON ha_a.id = hp.author_id
-            JOIN hive_permlink_data hpd_p ON hpd_p.id = hp.permlink_id
-            JOIN hive_post_data hpd ON hpd.id = hp.id
-            JOIN hive_accounts ha_pp ON ha_pp.id = pp.author_id
-            JOIN hive_permlink_data hpd_pp ON hpd_pp.id = pp.permlink_id
-            JOIN hive_accounts ha_rp ON ha_rp.id = rp.author_id
-            JOIN hive_permlink_data hpd_rp ON hpd_rp.id = rp.permlink_id
-            JOIN hive_post_data rpd ON rpd.id = rp.id
-            JOIN hive_category_data hcd ON hcd.id = hp.category_id
-            JOIN hive_category_data rcd ON rcd.id = rp.category_id
-            LEFT JOIN hive_communities hc ON hp.community_id = hc.id
-            LEFT JOIN hive_roles hr ON hp.author_id = hr.account_id AND hp.community_id = hr.community_id
-          WHERE hp.counter_deleted = 0;
-          """
-    db.query_no_return(sql)
-
-    sql = """
-          DROP FUNCTION IF EXISTS public.update_hive_posts_root_id(INTEGER, INTEGER);
-
-          CREATE OR REPLACE FUNCTION public.update_hive_posts_root_id(in _first_block_num INTEGER, _last_block_num INTEGER)
-              RETURNS void
-              LANGUAGE 'plpgsql'
-              VOLATILE
-          AS $BODY$
-          BEGIN
-
-          --- _first_block_num can be null together with _last_block_num
-          UPDATE hive_posts uhp
-          SET root_id = id
-          WHERE uhp.root_id = 0 AND (_first_block_num IS NULL OR (uhp.block_num >= _first_block_num AND uhp.block_num <= _last_block_num))
-          ;
-          END
-          $BODY$;
-          """
-    db.query_no_return(sql)
-
-    sql = """
-          DROP FUNCTION IF EXISTS public.update_hive_posts_children_count(INTEGER, INTEGER);
-
-          CREATE OR REPLACE FUNCTION public.update_hive_posts_children_count(in _first_block INTEGER, in _last_block INTEGER)
-              RETURNS void
-              LANGUAGE SQL
-              VOLATILE
-          AS $BODY$
-          UPDATE hive_posts uhp
-          SET children = data_source.children_count
-          FROM
-          (
-            WITH recursive tblChild AS
-            (
-              SELECT s.queried_parent, s.id
-              FROM
-              (SELECT h1.Parent_Id AS queried_parent, h1.id
-               FROM hive_posts h1
-               WHERE h1.depth > 0 AND h1.counter_deleted = 0
-                     AND h1.block_num BETWEEN _first_block AND _last_block
-               ORDER BY h1.depth DESC
-              ) s
-              UNION ALL
-              SELECT tblChild.queried_parent, p.id FROM hive_posts p
-              JOIN tblChild  ON p.Parent_Id = tblChild.Id
-              WHERE p.counter_deleted = 0
-            )
-            SELECT queried_parent, cast(count(1) AS int) AS children_count
-            FROM tblChild
-            GROUP BY queried_parent
-          ) data_source
-          WHERE uhp.id = data_source.queried_parent
-          ;
-          $BODY$;
-          """
-    db.query_no_return(sql)
-
-    sql = """
-        DROP VIEW IF EXISTS hive_votes_view
-        ;
-        CREATE OR REPLACE VIEW hive_votes_view
-        AS
-        SELECT
-            hv.voter_id as voter_id,
-            ha_a.name as author,
-            hpd.permlink as permlink,
-            vote_percent as percent,
-            ha_a.reputation as reputation,
-            rshares,
-            last_update,
-            ha_v.name as voter,
-            weight,
-            num_changes,
-            hv.permlink_id as permlink_id,
-            post_id,
-            is_effective
-        FROM
-            hive_votes hv
-        INNER JOIN hive_accounts ha_v ON ha_v.id = hv.voter_id
-        INNER JOIN hive_accounts ha_a ON ha_a.id = hv.author_id
-        INNER JOIN hive_permlink_data hpd ON hpd.id = hv.permlink_id
-        ;
-    """
-    db.query_no_return(sql)
-
-    sql = """
-        DROP TYPE IF EXISTS database_api_vote CASCADE;
-
-        CREATE TYPE database_api_vote AS (
-          voter VARCHAR(16),
-          author VARCHAR(16),
-          permlink VARCHAR(255),
-          weight NUMERIC,
-          rshares BIGINT,
-          percent INT,
-          last_update TIMESTAMP,
-          num_changes INT,
-          reputation FLOAT4
-        );
-
-        DROP FUNCTION IF EXISTS get_account(character varying, boolean);
-
-        CREATE OR REPLACE FUNCTION get_account(
-          in _account hive_accounts.name%TYPE,
-          in _check boolean)
-        RETURNS INT
-        LANGUAGE 'plpgsql'
-        AS
-        $function$
-        DECLARE
-          account_id INT;
-        BEGIN
-          SELECT INTO account_id COALESCE( ( SELECT id FROM hive_accounts WHERE name=_account ), 0 );
-          IF _check AND account_id = 0 THEN
-            RAISE EXCEPTION 'Account % does not exist', _account;
-          END IF;
-
-          RETURN account_id;
-        END
-        $function$
-        ;
-
-        DROP FUNCTION IF EXISTS list_votes_by_voter_comment( character varying, character varying, character varying, int );
-
-        CREATE OR REPLACE FUNCTION public.list_votes_by_voter_comment
-        (
-          in _VOTER hive_accounts.name%TYPE,
-          in _AUTHOR hive_accounts.name%TYPE,
-          in _PERMLINK hive_permlink_data.permlink%TYPE,
-          in _LIMIT INT
-        )
-        RETURNS SETOF database_api_vote
-        LANGUAGE 'plpgsql'
-        AS
-        $function$
-        DECLARE _VOTER_ID INT;
-        DECLARE _POST_ID INT;
-        BEGIN
-
-        _VOTER_ID = get_account( _VOTER, true );
-        _POST_ID = find_comment_id( _AUTHOR, _PERMLINK, True);
-
-        RETURN QUERY
-        (
-                SELECT
-                    v.voter,
-                    v.author,
-                    v.permlink,
-                    v.weight,
-                    v.rshares,
-                    v.percent,
-                    v.last_update,
-                    v.num_changes,
-                    v.reputation
-                FROM
-                    hive_votes_view v
-                    WHERE
-                        ( v.voter_id = _VOTER_ID and v.post_id >= _POST_ID )
-                        OR
-                        ( v.voter_id > _VOTER_ID )
-                    ORDER BY
-                      voter_id,
-                      post_id
-                LIMIT _LIMIT
-        );
-
-        END
-        $function$;
-
-        DROP FUNCTION IF EXISTS list_votes_by_comment_voter( character varying, character varying, character varying, int );
-
-        CREATE OR REPLACE FUNCTION public.list_votes_by_comment_voter
-        (
-          in _VOTER hive_accounts.name%TYPE,
-          in _AUTHOR hive_accounts.name%TYPE,
-          in _PERMLINK hive_permlink_data.permlink%TYPE,
-          in _LIMIT INT
-        )
-        RETURNS SETOF database_api_vote
-        LANGUAGE 'plpgsql'
-        AS
-        $function$
-        DECLARE _VOTER_ID INT;
-        DECLARE _POST_ID INT;
-        BEGIN
-
-        _VOTER_ID = get_account( _VOTER, true );
-        _POST_ID = find_comment_id( _AUTHOR, _PERMLINK, True);
-
-        RETURN QUERY
-        (
-                SELECT
-                    v.voter,
-                    v.author,
-                    v.permlink,
-                    v.weight,
-                    v.rshares,
-                    v.percent,
-                    v.last_update,
-                    v.num_changes,
-                    v.reputation
-                FROM
-                    hive_votes_view v
-                    WHERE
-                        ( v.post_id = _POST_ID and v.voter_id >= _VOTER_ID )
-                        OR
-                        ( v.post_id > _POST_ID )
-                    ORDER BY
-                      post_id,
-                      voter_id
-                LIMIT _LIMIT
-        );
-
-        END
-        $function$;
-    """
-    db.query_no_return(sql)
-
-    sql = """
-      DROP FUNCTION IF EXISTS find_comment_id(character varying, character varying, boolean)
-      ;
-      CREATE OR REPLACE FUNCTION find_comment_id(
-        in _author hive_accounts.name%TYPE,
-        in _permlink hive_permlink_data.permlink%TYPE,
-        in _check boolean)
-      RETURNS INT
-      LANGUAGE 'plpgsql'
-      AS
-      $function$
-      DECLARE
-        post_id INT;
-      BEGIN
-        SELECT INTO post_id COALESCE( (SELECT hp.id
-        FROM hive_posts hp
-        JOIN hive_accounts ha ON ha.id = hp.author_id
-        JOIN hive_permlink_data hpd ON hpd.id = hp.permlink_id
-        WHERE ha.name = _author AND hpd.permlink = _permlink AND hp.counter_deleted = 0
-        ), 0 );
-        IF _check AND (_author <> '' OR _permlink <> '') AND post_id = 0 THEN
-          RAISE EXCEPTION 'Post %/% does not exist', _author, _permlink;
-        END IF;
-        RETURN post_id;
-      END
-      $function$
-      ;
-    """
-    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),
-        last_payout_at TIMESTAMP,
-        cashout_time TIMESTAMP,
-        is_paidout BOOLEAN,
-        children INT,
-        votes INT,
-        created_at TIMESTAMP,
-        updated_at TIMESTAMP,
-        rshares NUMERIC,
-        json TEXT,
-        is_hidden BOOLEAN,
-        is_grayed BOOLEAN,
-        total_votes BIGINT,
-        net_votes BIGINT,
-        total_vote_weight NUMERIC,
-        parent_author VARCHAR(16),
-        parent_permlink_or_category 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 NUMERIC,
-        active TIMESTAMP,
-        author_rewards BIGINT
-      )
-      ;
-
-      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$
-        DECLARE
-          __post_id INT;
-        BEGIN
-          __post_id = find_comment_id(_author,_permlink, False);
-          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.last_payout_at, hp.cashout_time, 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.net_votes, hp.total_vote_weight,
-              hp.parent_author, hp.parent_permlink_or_category, 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
-          FROM
-              hive_posts_view hp
-          WHERE
-              NOT hp.is_muted AND
-              hp.cashout_time > _cashout_time OR
-              hp.cashout_time = _cashout_time AND hp.id >= __post_id
-          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.last_payout_at, hp.cashout_time, 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.net_votes, hp.total_vote_weight,
-              hp.parent_author, hp.parent_permlink_or_category, 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
-          FROM
-              hive_posts_view hp
-          WHERE
-              NOT hp.is_muted AND
-              hp.author > _author OR
-              hp.author = _author AND hp.permlink >= _permlink
-          ORDER BY
-              hp.author ASC,
-              hp.permlink 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$
-        DECLARE
-          __root_id INT;
-          __post_id INT;
-        BEGIN
-          __root_id = find_comment_id(_root_author, _root_permlink, True);
-          __post_id = find_comment_id(_start_post_author, _start_post_permlink, True);
-          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.last_payout_at, hp.cashout_time, 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.net_votes, hp.total_vote_weight,
-            hp.parent_author, hp.parent_permlink_or_category, 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
-          FROM hive_posts_view hp
-          INNER JOIN
-          (
-          SELECT hp2.id, hp2.root_id FROM hive_posts hp2
-          WHERE NOT hp2.is_muted
-                AND hp2.root_id > __root_id
-                OR hp2.root_id = __root_id AND hp2.id >= __post_id AND hp2.id > 0
-          ORDER BY
-             hp2.root_id ASC
-            ,hp2.id ASC
-          LIMIT _limit
-          ) ds on hp.id = ds.id
-          ;
-        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
-        LANGUAGE sql
-        COST 100
-        STABLE
-        ROWS 1000
-      AS $function$
-        SELECT
-          hp.id, hp.community_id, hp.author, hp.permlink, hp.title, hp.body,
-          hp.category, hp.depth, hp.promoted, hp.payout, hp.last_payout_at, hp.cashout_time, 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.net_votes, hp.total_vote_weight,
-          hp.parent_author, hp.parent_permlink_or_category, 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
-        FROM
-          hive_posts_view hp
-          INNER JOIN
-          (
-            SELECT h.id FROM
-            hive_posts_api_helper h
-            WHERE
-              h.parent_author > _parent_author OR
-              h.parent_author = _parent_author AND ( h.parent_permlink_or_category > _parent_permlink OR
-              h.parent_permlink_or_category = _parent_permlink AND h.id >= find_comment_id(_start_post_author, _start_post_permlink, True) )
-            ORDER BY
-              h.parent_author ASC,
-              h.parent_permlink_or_category ASC,
-              h.id ASC
-            LIMIT
-              _limit
-          ) ds ON ds.id = hp.id
-        WHERE
-          NOT hp.is_muted
-          ;
-      $function$
-      ;
-
-      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$
-        DECLARE
-          __post_id INT;
-        BEGIN
-          __post_id = find_comment_id(_start_post_author, _start_post_permlink, True);
-          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.last_payout_at, hp.cashout_time, 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.net_votes, hp.total_vote_weight,
-              hp.parent_author, hp.parent_permlink_or_category, 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
-          FROM
-              hive_posts_view hp
-          WHERE
-              NOT hp.is_muted AND
-              hp.parent_author > _parent_author OR
-              hp.parent_author = _parent_author AND ( hp.updated_at < _updated_at OR
-              hp.updated_at = _updated_at AND hp.id >= __post_id )
-          ORDER BY
-              hp.parent_author ASC,
-              hp.updated_at DESC,
-              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$
-        DECLARE
-          __post_id INT;
-        BEGIN
-          __post_id = find_comment_id(_start_post_author, _start_post_permlink, True);
-          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.last_payout_at, hp.cashout_time, 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.net_votes, hp.total_vote_weight,
-              hp.parent_author, hp.parent_permlink_or_category, 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
-          FROM
-              hive_posts_view hp
-          WHERE
-              NOT hp.is_muted AND
-              -- fat node used wrong index (by_last_update) so the results are vastly different
-              hp.author > _author OR
-              hp.author = _author AND ( hp.updated_at < _updated_at OR
-              hp.updated_at = _updated_at AND hp.id >= __post_id )
-          ORDER BY
-              hp.author ASC,
-              hp.updated_at DESC,
-              hp.id ASC
-          LIMIT
-              _limit
-          ;
-        END
-        $function$
-        LANGUAGE plpgsql
-      ;
-    """
-
-    db.query_no_return(sql)
-
-    sql = """
-        DROP FUNCTION IF EXISTS score_for_account(in _account_id hive_accounts.id%TYPE)
-        ;
-        CREATE OR REPLACE FUNCTION score_for_account(in _account_id hive_accounts.id%TYPE)
-        RETURNS SMALLINT
-        AS
-        $function$
-        DECLARE
-          score SMALLINT;
-        BEGIN
-          SELECT INTO score
-             CASE
-                 WHEN rank.position < 200 THEN 70
-                 WHEN rank.position < 1000 THEN 60
-                 WHEN rank.position < 6500 THEN 50
-                 WHEN rank.position < 25000 THEN 40
-                 WHEN rank.position < 100000 THEN 30
-                 ELSE 20
-             END as score
-          FROM (
-             SELECT
-                 (
-                     SELECT COUNT(*)
-                     FROM hive_accounts ha_for_rank2
-                     WHERE ha_for_rank2.reputation > ha_for_rank.reputation
-                 ) as position
-             FROM hive_accounts ha_for_rank WHERE ha_for_rank.id = _account_id
-          ) as rank;
-          return score;
-        END
-        $function$
-        LANGUAGE plpgsql
-        ;
-    """
-    db.query_no_return(sql)
-
-    # hot and tranding functions
-
-    sql = """
-       DROP FUNCTION IF EXISTS date_diff() CASCADE
-       ;
-       CREATE OR REPLACE FUNCTION date_diff (units VARCHAR(30), start_t TIMESTAMP, end_t TIMESTAMP)
-         RETURNS INT AS $$
-       DECLARE
-         diff_interval INTERVAL;
-         diff INT = 0;
-         years_diff INT = 0;
-       BEGIN
-         IF units IN ('yy', 'yyyy', 'year', 'mm', 'm', 'month') THEN
-           years_diff = DATE_PART('year', end_t) - DATE_PART('year', start_t);
-           IF units IN ('yy', 'yyyy', 'year') THEN
-             -- SQL Server does not count full years passed (only difference between year parts)
-             RETURN years_diff;
-           ELSE
-             -- If end month is less than start month it will subtracted
-             RETURN years_diff * 12 + (DATE_PART('month', end_t) - DATE_PART('month', start_t));
-           END IF;
-         END IF;
-         -- Minus operator returns interval 'DDD days HH:MI:SS'
-         diff_interval = end_t - start_t;
-         diff = diff + DATE_PART('day', diff_interval);
-         IF units IN ('wk', 'ww', 'week') THEN
-           diff = diff/7;
-           RETURN diff;
-         END IF;
-         IF units IN ('dd', 'd', 'day') THEN
-           RETURN diff;
-         END IF;
-         diff = diff * 24 + DATE_PART('hour', diff_interval);
-         IF units IN ('hh', 'hour') THEN
-            RETURN diff;
-         END IF;
-         diff = diff * 60 + DATE_PART('minute', diff_interval);
-         IF units IN ('mi', 'n', 'minute') THEN
-            RETURN diff;
-         END IF;
-         diff = diff * 60 + DATE_PART('second', diff_interval);
-         RETURN diff;
-       END;
-       $$ LANGUAGE plpgsql IMMUTABLE
-    """
-    db.query_no_return(sql)
-
-    sql = """
-          DROP FUNCTION IF EXISTS public.calculate_time_part_of_trending(_post_created_at hive_posts.created_at%TYPE ) CASCADE
-          ;
-          CREATE OR REPLACE FUNCTION public.calculate_time_part_of_trending(
-            _post_created_at hive_posts.created_at%TYPE)
-              RETURNS double precision
-              LANGUAGE 'plpgsql'
-              IMMUTABLE
-          AS $BODY$
-          DECLARE
-            result double precision;
-            sec_from_epoch INT = 0;
-          BEGIN
-            sec_from_epoch  = date_diff( 'second', CAST('19700101' AS TIMESTAMP), _post_created_at );
-            result = sec_from_epoch/240000.0;
-            return result;
-          END;
-          $BODY$;
-    """
-    db.query_no_return(sql)
-
-    sql = """
-            DROP FUNCTION IF EXISTS public.calculate_time_part_of_hot(_post_created_at hive_posts.created_at%TYPE ) CASCADE
-            ;
-            CREATE OR REPLACE FUNCTION public.calculate_time_part_of_hot(
-              _post_created_at hive_posts.created_at%TYPE)
-                RETURNS double precision
-                LANGUAGE 'plpgsql'
-                IMMUTABLE
-            AS $BODY$
-            DECLARE
-              result double precision;
-              sec_from_epoch INT = 0;
-            BEGIN
-              sec_from_epoch  = date_diff( 'second', CAST('19700101' AS TIMESTAMP), _post_created_at );
-              result = sec_from_epoch/10000.0;
-              return result;
-            END;
-            $BODY$;
-    """
-    db.query_no_return(sql)
-
-    sql = """
-    DROP FUNCTION IF EXISTS public.calculate_rhsares_part_of_hot_and_trend(_rshares hive_votes.rshares%TYPE) CASCADE
-    ;
-    CREATE OR REPLACE FUNCTION public.calculate_rhsares_part_of_hot_and_trend(_rshares hive_votes.rshares%TYPE)
-    RETURNS double precision
-    LANGUAGE 'plpgsql'
-    IMMUTABLE
-    AS $BODY$
-    DECLARE
-        mod_score double precision;
-    BEGIN
-        mod_score := _rshares / 10000000.0;
-        IF ( mod_score > 0 )
-        THEN
-            return log( greatest( abs(mod_score), 1 ) );
-        END IF;
-        return  -1.0 * log( greatest( abs(mod_score), 1 ) );
-    END;
-    $BODY$;
-    """
-    db.query_no_return(sql)
-
-    sql = """
-    DROP FUNCTION IF EXISTS public.calculate_hot(hive_votes.rshares%TYPE, hive_posts.created_at%TYPE)
-    ;
-    CREATE OR REPLACE FUNCTION public.calculate_hot(
-        _rshares hive_votes.rshares%TYPE,
-        _post_created_at hive_posts.created_at%TYPE)
-    RETURNS hive_posts.sc_hot%TYPE
-    LANGUAGE 'plpgsql'
-    IMMUTABLE
-    AS $BODY$
-    BEGIN
-        return calculate_rhsares_part_of_hot_and_trend(_rshares) + calculate_time_part_of_hot( _post_created_at );
-    END;
-    $BODY$;
-    """
-    db.query_no_return(sql)
-
-    sql = """
-          DO $$
-          BEGIN
-            EXECUTE 'ALTER DATABASE '||current_database()||' SET join_collapse_limit TO 16';
-            EXECUTE 'ALTER DATABASE '||current_database()||' SET from_collapse_limit TO 16';
-          END
-          $$;
-          """
-    db.query_no_return(sql)
-
-    sql = """
-    DROP FUNCTION IF EXISTS public.calculate_tranding(hive_votes.rshares%TYPE, hive_posts.created_at%TYPE)
-    ;
-    CREATE OR REPLACE FUNCTION public.calculate_tranding(
-        _rshares hive_votes.rshares%TYPE,
-        _post_created_at hive_posts.created_at%TYPE)
-    RETURNS hive_posts.sc_trend%TYPE
-    LANGUAGE 'plpgsql'
-    IMMUTABLE
-    AS $BODY$
-    BEGIN
-        return calculate_rhsares_part_of_hot_and_trend(_rshares) + calculate_time_part_of_trending( _post_created_at );
-    END;
-    $BODY$;
-    """
-    db.query_no_return(sql)
-
-    sql = """
-        DROP FUNCTION IF EXISTS public.max_time_stamp() CASCADE;
-        CREATE OR REPLACE FUNCTION public.max_time_stamp( _first TIMESTAMP, _second TIMESTAMP )
-        RETURNS TIMESTAMP
-        LANGUAGE 'plpgsql'
-        IMMUTABLE
-        AS $BODY$
-        BEGIN
-          IF _first > _second THEN
-               RETURN _first;
-            ELSE
-               RETURN _second;
-            END IF;
-        END
-        $BODY$;
-        """
-    db.query_no_return(sql)
-
-    sql = """
-          DROP FUNCTION IF EXISTS public.update_hive_posts_api_helper(INTEGER, INTEGER);
-
-          CREATE OR REPLACE FUNCTION public.update_hive_posts_api_helper(in _first_block_num INTEGER, _last_block_num INTEGER)
-            RETURNS void
-            LANGUAGE 'plpgsql'
-            VOLATILE
-          AS $BODY$
-          BEGIN
-          IF _first_block_num IS NULL OR _last_block_num IS NULL THEN
-            -- initial creation of table.
-
-            INSERT INTO hive_posts_api_helper
-            (id, author, parent_author, parent_permlink_or_category)
-            SELECT hp.id, hp.author, hp.parent_author, hp.parent_permlink_or_category
-            FROM hive_posts_view hp
-            ;
-          ELSE
-            -- Regular incremental update.
-            INSERT INTO hive_posts_api_helper
-            (id, author, parent_author, parent_permlink_or_category)
-            SELECT hp.id, hp.author, hp.parent_author, hp.parent_permlink_or_category
-            FROM hive_posts_view hp
-            WHERE hp.block_num BETWEEN _first_block_num AND _last_block_num AND
-                   NOT EXISTS (SELECT NULL FROM hive_posts_api_helper h WHERE h.id = hp.id)
-            ;
-          END IF;
-
-          END
-          $BODY$
-          """
-    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)
-    sql = """
-        DROP FUNCTION IF EXISTS public.calculate_notify_vote_score(_payout hive_posts.payout%TYPE, _abs_rshares hive_posts_view.abs_rshares%TYPE, _rshares hive_votes.rshares%TYPE) CASCADE
-        ;
-        CREATE OR REPLACE FUNCTION public.calculate_notify_vote_score(_payout hive_posts.payout%TYPE, _abs_rshares hive_posts_view.abs_rshares%TYPE, _rshares hive_votes.rshares%TYPE)
-        RETURNS INT
-        LANGUAGE 'sql'
-        IMMUTABLE
-        AS $BODY$
-            SELECT CASE
-                WHEN ((( _payout )/_abs_rshares) * 1000 * _rshares < 20 ) THEN -1
-                ELSE LEAST(100, (LENGTH(CAST( ( (( _payout )/_abs_rshares) * 1000 * _rshares ) as text)) - 1) * 25)
-            END;
-        $BODY$;
-    """
-
-    db.query_no_return(sql)
-
-    sql = """
-        DROP FUNCTION IF EXISTS notification_id(in _block_number INTEGER, in _notifyType INTEGER, in _id INTEGER)
-        ;
-        CREATE OR REPLACE FUNCTION notification_id(in _block_number INTEGER, in _notifyType INTEGER, in _id INTEGER)
-        RETURNS BIGINT
-        AS
-        $function$
-        BEGIN
-        RETURN CAST( _block_number as BIGINT ) << 32
-        	  | ( _notifyType << 16 )
-        	  | ( _id & CAST( x'00FF' as INTEGER) );
-        END
-        $function$
-        LANGUAGE plpgsql IMMUTABLE
-        ;
-    """
-    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.
-
-    We use a scale factor of 0 and specify exact threshold tuple counts,
-    per-table, in the format (autovacuum_threshold, autoanalyze_threshold)."""
-
-    autovac_config = { #    vacuum  analyze
-        'hive_accounts':    (50000, 100000),
-        'hive_posts':       (2500, 10000),
-        'hive_post_tags':   (5000, 10000),
-        'hive_follows':     (5000, 5000),
-        'hive_feed_cache':  (5000, 5000),
-        'hive_blocks':      (5000, 25000),
-        'hive_reblogs':     (5000, 5000),
-        'hive_payments':    (5000, 5000),
-    }
-
-    for table, (n_vacuum, n_analyze) in autovac_config.items():
-        sql = """ALTER TABLE %s SET (autovacuum_vacuum_scale_factor = 0,
-                                     autovacuum_vacuum_threshold = %s,
-                                     autovacuum_analyze_scale_factor = 0,
-                                     autovacuum_analyze_threshold = %s)"""
-        db.query(sql % (table, n_vacuum, n_analyze))
-
-
-def set_fillfactor(db):
-    """Initializes/resets FILLFACTOR for tables which are intesively updated"""
-
-    fillfactor_config = {
-        'hive_posts': 70,
-        'hive_post_data': 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'))
-
-def execute_sql_script(query_executor, path_to_script):
-    """ 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;
-          will return something like: (0, 18, Decimal('0.000000'), Decimal('0.000000'), Decimal('0.000000'), '')
-    """
-    try:
-        sql_script = None
-        with open(path_to_script, 'r') as sql_script_file:
-            sql_script = sql_script_file.read()
-        if sql_script is not None:
-            return query_executor(sql_script)
-    except Exception as ex:
-        log.exception("Error running sql script: {}".format(ex))
-        raise ex
-    return None
+"""Db schema definitions and setup routines."""
+
+import sqlalchemy as sa
+from sqlalchemy.sql import text as sql_text
+from sqlalchemy.types import SMALLINT
+from sqlalchemy.types import CHAR
+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
+DB_VERSION = 18
+
+def build_metadata():
+    """Build schema def with SqlAlchemy"""
+    metadata = sa.MetaData()
+
+    sa.Table(
+        'hive_blocks', metadata,
+        sa.Column('num', sa.Integer, primary_key=True, autoincrement=False),
+        sa.Column('hash', CHAR(40), nullable=False),
+        sa.Column('prev', CHAR(40)),
+        sa.Column('txs', SMALLINT, server_default='0', nullable=False),
+        sa.Column('ops', SMALLINT, server_default='0', nullable=False),
+        sa.Column('created_at', sa.DateTime, nullable=False),
+
+        sa.UniqueConstraint('hash', name='hive_blocks_ux1'),
+        sa.ForeignKeyConstraint(['prev'], ['hive_blocks.hash'], name='hive_blocks_fk1'),
+    )
+
+    sa.Table(
+        'hive_accounts', metadata,
+        sa.Column('id', sa.Integer, primary_key=True),
+        sa.Column('name', VARCHAR(16, collation='C'), nullable=False),
+        sa.Column('created_at', sa.DateTime, nullable=False),
+        #sa.Column('block_num', sa.Integer, nullable=False),
+        sa.Column('reputation', sa.Float(precision=6), nullable=False, server_default='25'),
+
+        sa.Column('display_name', sa.String(20)),
+        sa.Column('about', sa.String(160)),
+        sa.Column('location', sa.String(30)),
+        sa.Column('website', sa.String(1024)),
+        sa.Column('profile_image', sa.String(1024), nullable=False, server_default=''),
+        sa.Column('cover_image', sa.String(1024), nullable=False, server_default=''),
+
+        sa.Column('followers', sa.Integer, nullable=False, server_default='0'),
+        sa.Column('following', sa.Integer, nullable=False, server_default='0'),
+
+        sa.Column('proxy', VARCHAR(16), nullable=False, server_default=''),
+        sa.Column('proxy_weight', sa.Float(precision=6), nullable=False, server_default='0'),
+        sa.Column('kb_used', sa.Integer, nullable=False, server_default='0'), # deprecated
+        sa.Column('rank', sa.Integer, nullable=False, server_default='0'),
+
+        sa.Column('lastread_at', sa.DateTime, nullable=False, server_default='1970-01-01 00:00:00'),
+        sa.Column('cached_at', sa.DateTime, nullable=False, server_default='1970-01-01 00:00:00'),
+        sa.Column('raw_json', sa.Text),
+
+        sa.UniqueConstraint('name', name='hive_accounts_ux1'),
+        sa.Index('hive_accounts_ix5', 'cached_at'), # core/listen sweep
+        sa.Index('hive_accounts_ix6', 'reputation')
+    )
+
+
+    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),
+        sa.Column('root_id', sa.Integer, nullable=False), # records having initially set 0 will be updated to their id
+        sa.Column('parent_id', sa.Integer, nullable=False),
+        sa.Column('author_id', sa.Integer, nullable=False),
+        sa.Column('permlink_id', sa.Integer, nullable=False),
+        sa.Column('category_id', sa.Integer, nullable=False),
+        sa.Column('community_id', sa.Integer, nullable=True),
+        sa.Column('created_at', sa.DateTime, nullable=False),
+        sa.Column('depth', SMALLINT, nullable=False),
+        sa.Column('counter_deleted', sa.Integer, nullable=False, server_default='0'),
+        sa.Column('is_pinned', BOOLEAN, nullable=False, server_default='0'),
+        sa.Column('is_muted', BOOLEAN, nullable=False, server_default='0'),
+        sa.Column('is_valid', BOOLEAN, nullable=False, server_default='1'),
+        sa.Column('promoted', sa.types.DECIMAL(10, 3), nullable=False, server_default='0'),
+
+        sa.Column('children', sa.Integer, nullable=False, server_default='0'),
+
+
+        # core stats/indexes
+        sa.Column('payout', sa.types.DECIMAL(10, 3), nullable=False, server_default='0'),
+        sa.Column('pending_payout', sa.types.DECIMAL(10, 3), nullable=False, server_default='0'),
+        sa.Column('payout_at', sa.DateTime, nullable=False, server_default='1970-01-01'),
+        sa.Column('last_payout_at', sa.DateTime, nullable=False, server_default='1970-01-01'),
+        sa.Column('updated_at', sa.DateTime, nullable=False, server_default='1970-01-01'),
+        sa.Column('is_paidout', BOOLEAN, nullable=False, server_default='0'),
+
+        # ui flags/filters
+        sa.Column('is_nsfw', BOOLEAN, nullable=False, server_default='0'),
+        sa.Column('is_declined', BOOLEAN, nullable=False, server_default='0'),
+        sa.Column('is_full_power', BOOLEAN, nullable=False, server_default='0'),
+        sa.Column('is_hidden', BOOLEAN, nullable=False, server_default='0'),
+        sa.Column('is_grayed', BOOLEAN, nullable=False, server_default='0'),
+
+        # important indexes
+        sa.Column('sc_trend', sa.Float(precision=6), nullable=False, server_default='0'),
+        sa.Column('sc_hot', sa.Float(precision=6), nullable=False, server_default='0'),
+
+        sa.Column('total_payout_value', sa.String(30), nullable=False, server_default='0.000 HBD'),
+        sa.Column('author_rewards', sa.BigInteger, nullable=False, server_default='0'),
+
+        sa.Column('author_rewards_hive', sa.BigInteger, nullable=False, server_default='0'),
+        sa.Column('author_rewards_hbd', sa.BigInteger, nullable=False, server_default='0'),
+        sa.Column('author_rewards_vests', sa.BigInteger, nullable=False, server_default='0'),
+
+        sa.Column('abs_rshares', sa.BigInteger, nullable=False, server_default='0'),
+        sa.Column('vote_rshares', sa.BigInteger, nullable=False, server_default='0'),
+        sa.Column('total_vote_weight', sa.Numeric, nullable=False, server_default='0'),
+        sa.Column('active', 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('percent_hbd', sa.Integer, nullable=False, server_default='10000'),
+
+        sa.Column('curator_payout_value', sa.String(30), nullable=False, server_default='0.000 HBD'),
+        sa.Column('max_accepted_payout',  sa.String(30), nullable=False, server_default='1000000.000 HBD'),
+        sa.Column('allow_votes', BOOLEAN, nullable=False, server_default='1'),
+        sa.Column('allow_curation_rewards', BOOLEAN, nullable=False, server_default='1'),
+        sa.Column('beneficiaries', sa.JSON, nullable=False, server_default='[]'),
+        sa.Column('block_num', sa.Integer,  nullable=False ),
+
+        sa.ForeignKeyConstraint(['author_id'], ['hive_accounts.id'], name='hive_posts_fk1'),
+        sa.ForeignKeyConstraint(['root_id'], ['hive_posts.id'], name='hive_posts_fk2'),
+        sa.ForeignKeyConstraint(['parent_id'], ['hive_posts.id'], name='hive_posts_fk3'),
+        sa.UniqueConstraint('author_id', 'permlink_id', 'counter_deleted', name='hive_posts_ux1'),
+
+        sa.Index('hive_posts_depth_idx', 'depth'),
+
+        sa.Index('hive_posts_root_id_id_idx', 'root_id','id'),
+
+        sa.Index('hive_posts_parent_id_idx', 'parent_id'),
+        sa.Index('hive_posts_community_id_idx', 'community_id'),
+
+        sa.Index('hive_posts_category_id_idx', 'category_id'),
+        sa.Index('hive_posts_payout_at_idx', 'payout_at'),
+        sa.Index('hive_posts_payout_idx', 'payout'),
+        sa.Index('hive_posts_promoted_idx', 'promoted'),
+        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_block_num_idx', 'block_num')
+    )
+
+    sa.Table(
+        'hive_post_data', metadata,
+        sa.Column('id', sa.Integer, primary_key=True, autoincrement=False),
+        sa.Column('title', VARCHAR(512), nullable=False, server_default=''),
+        sa.Column('preview', VARCHAR(1024), nullable=False, server_default=''), # first 1k of 'body'
+        sa.Column('img_url', VARCHAR(1024), nullable=False, server_default=''), # first 'image' from 'json'
+        sa.Column('body', TEXT, nullable=False, server_default=''),
+        sa.Column('json', TEXT, nullable=False, server_default='')
+    )
+
+    sa.Table(
+        'hive_permlink_data', metadata,
+        sa.Column('id', sa.Integer, primary_key=True),
+        sa.Column('permlink', sa.String(255, collation='C'), nullable=False),
+        sa.UniqueConstraint('permlink', name='hive_permlink_data_permlink')
+    )
+
+    sa.Table(
+        'hive_category_data', metadata,
+        sa.Column('id', sa.Integer, primary_key=True),
+        sa.Column('category', sa.String(255, collation='C'), nullable=False),
+        sa.UniqueConstraint('category', name='hive_category_data_category')
+    )
+
+    sa.Table(
+        'hive_votes', metadata,
+        sa.Column('id', sa.BigInteger, primary_key=True),
+        sa.Column('post_id', sa.Integer, nullable=False),
+        sa.Column('voter_id', sa.Integer, nullable=False),
+        sa.Column('author_id', sa.Integer, nullable=False),
+        sa.Column('permlink_id', sa.Integer, nullable=False),
+        sa.Column('weight', sa.Numeric, nullable=False, server_default='0'),
+        sa.Column('rshares', sa.BigInteger, nullable=False, server_default='0'),
+        sa.Column('vote_percent', sa.Integer, server_default='0'),
+        sa.Column('last_update', sa.DateTime, nullable=False, server_default='1970-01-01 00:00:00'),
+        sa.Column('num_changes', sa.Integer, server_default='0'),
+        sa.Column('block_num', sa.Integer,  nullable=False ),
+        sa.Column('is_effective', BOOLEAN, nullable=False, server_default='0'),
+
+        sa.UniqueConstraint('voter_id', 'author_id', 'permlink_id', name='hive_votes_ux1'),
+
+        sa.ForeignKeyConstraint(['post_id'], ['hive_posts.id'], name='hive_votes_fk1'),
+        sa.ForeignKeyConstraint(['voter_id'], ['hive_accounts.id'], name='hive_votes_fk2'),
+        sa.ForeignKeyConstraint(['author_id'], ['hive_accounts.id'], name='hive_votes_fk3'),
+        sa.ForeignKeyConstraint(['permlink_id'], ['hive_permlink_data.id'], name='hive_votes_fk4'),
+        sa.ForeignKeyConstraint(['block_num'], ['hive_blocks.num'], name='hive_votes_fk5'),
+
+        sa.Index('hive_votes_post_id_idx', 'post_id'),
+        sa.Index('hive_votes_voter_id_idx', 'voter_id'),
+        sa.Index('hive_votes_voter_id_post_id_idx', 'voter_id', 'post_id'),
+        sa.Index('hive_votes_post_id_voter_id_idx', 'post_id', 'voter_id'),
+        sa.Index('hive_votes_block_num_idx', 'block_num')
+    )
+
+    sa.Table(
+        'hive_tag_data', metadata,
+        sa.Column('id', sa.Integer, nullable=False, primary_key=True),
+        sa.Column('tag', VARCHAR(64, collation='C'), nullable=False, server_default=''),
+        sa.UniqueConstraint('tag', name='hive_tag_data_ux1')
+    )
+
+    sa.Table(
+        'hive_post_tags', 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_tag_id_idx', 'tag_id')
+    )
+
+    sa.Table(
+        'hive_follows', metadata,
+        sa.Column('id', sa.Integer, primary_key=True ),
+        sa.Column('follower', sa.Integer, nullable=False),
+        sa.Column('following', sa.Integer, nullable=False),
+        sa.Column('state', SMALLINT, nullable=False, server_default='1'),
+        sa.Column('created_at', sa.DateTime, nullable=False),
+        sa.Column('blacklisted', sa.Boolean, nullable=False, server_default='0'),
+        sa.Column('follow_blacklists', sa.Boolean, nullable=False, server_default='0'),
+        sa.Column('block_num', sa.Integer,  nullable=False ),
+
+        sa.UniqueConstraint('following', 'follower', name='hive_follows_ux1'), # core
+        sa.ForeignKeyConstraint(['block_num'], ['hive_blocks.num'], name='hive_follows_fk1'),
+        sa.Index('hive_follows_ix5a', 'following', 'state', 'created_at', 'follower'),
+        sa.Index('hive_follows_ix5b', 'follower', 'state', 'created_at', 'following'),
+        sa.Index('hive_follows_block_num_idx', 'block_num')
+    )
+
+    sa.Table(
+        'hive_reblogs', metadata,
+        sa.Column('id', sa.Integer, primary_key=True ),
+        sa.Column('account', VARCHAR(16), nullable=False),
+        sa.Column('post_id', sa.Integer, nullable=False),
+        sa.Column('created_at', sa.DateTime, nullable=False),
+        sa.Column('block_num', sa.Integer,  nullable=False ),
+
+        sa.ForeignKeyConstraint(['account'], ['hive_accounts.name'], name='hive_reblogs_fk1'),
+        sa.ForeignKeyConstraint(['post_id'], ['hive_posts.id'], name='hive_reblogs_fk2'),
+        sa.ForeignKeyConstraint(['block_num'], ['hive_blocks.num'], name='hive_reblogs_fk3'),
+        sa.UniqueConstraint('account', 'post_id', name='hive_reblogs_ux1'), # core
+        sa.Index('hive_reblogs_account', 'account'),
+        sa.Index('hive_reblogs_post_id', 'post_id'),
+        sa.Index('hive_reblogs_block_num_idx', 'block_num')
+    )
+
+    sa.Table(
+        'hive_payments', metadata,
+        sa.Column('id', sa.Integer, primary_key=True),
+        sa.Column('block_num', sa.Integer, nullable=False),
+        sa.Column('tx_idx', SMALLINT, nullable=False),
+        sa.Column('post_id', sa.Integer, nullable=False),
+        sa.Column('from_account', sa.Integer, nullable=False),
+        sa.Column('to_account', sa.Integer, nullable=False),
+        sa.Column('amount', sa.types.DECIMAL(10, 3), nullable=False),
+        sa.Column('token', VARCHAR(5), nullable=False),
+
+        sa.ForeignKeyConstraint(['from_account'], ['hive_accounts.id'], name='hive_payments_fk1'),
+        sa.ForeignKeyConstraint(['to_account'], ['hive_accounts.id'], name='hive_payments_fk2'),
+        sa.ForeignKeyConstraint(['post_id'], ['hive_posts.id'], name='hive_payments_fk3'),
+        sa.Index('hive_payments_from', 'from_account'),
+        sa.Index('hive_payments_to', 'to_account'),
+        sa.Index('hive_payments_post_id', 'post_id'),
+    )
+
+    sa.Table(
+        'hive_feed_cache', metadata,
+        sa.Column('post_id', sa.Integer, nullable=False, primary_key=True),
+        sa.Column('account_id', sa.Integer, nullable=False),
+        sa.Column('created_at', sa.DateTime, nullable=False),
+        sa.Index('hive_feed_cache_account_id', 'account_id'), # API (and rebuild?)
+        sa.UniqueConstraint('account_id', 'post_id', name='hive_feed_cache_ux1')
+    )
+
+    sa.Table(
+        'hive_state', metadata,
+        sa.Column('block_num', sa.Integer, primary_key=True, autoincrement=False),
+        sa.Column('db_version', sa.Integer, nullable=False),
+        sa.Column('steem_per_mvest', sa.types.DECIMAL(14, 6), nullable=False),
+        sa.Column('usd_per_steem', sa.types.DECIMAL(14, 6), nullable=False),
+        sa.Column('sbd_per_steem', sa.types.DECIMAL(14, 6), nullable=False),
+        sa.Column('dgpo', sa.Text, nullable=False),
+    )
+
+    sa.Table(
+        'hive_posts_api_helper', metadata,
+        sa.Column('id', sa.Integer, primary_key=True, autoincrement = False),
+        sa.Column('author', VARCHAR(16, collation='C'), nullable=False),
+        sa.Column('parent_author', VARCHAR(16, collation='C'), nullable=False),
+        sa.Column('parent_permlink_or_category', sa.String(255, collation='C'), nullable=False),
+        sa.Index('hive_posts_api_helper_parent_permlink_or_category', 'parent_author', 'parent_permlink_or_category', 'id')
+    )
+
+    metadata = build_metadata_community(metadata)
+
+    return metadata
+
+def build_metadata_community(metadata=None):
+    """Build community schema defs"""
+    if not metadata:
+        metadata = sa.MetaData()
+
+    sa.Table(
+        'hive_communities', metadata,
+        sa.Column('id',          sa.Integer,      primary_key=True, autoincrement=False),
+        sa.Column('type_id',     SMALLINT,        nullable=False),
+        sa.Column('lang',        CHAR(2),         nullable=False, server_default='en'),
+        sa.Column('name',        VARCHAR(16, collation='C'), nullable=False),
+        sa.Column('title',       sa.String(32),   nullable=False, server_default=''),
+        sa.Column('created_at',  sa.DateTime,     nullable=False),
+        sa.Column('sum_pending', sa.Integer,      nullable=False, server_default='0'),
+        sa.Column('num_pending', sa.Integer,      nullable=False, server_default='0'),
+        sa.Column('num_authors', sa.Integer,      nullable=False, server_default='0'),
+        sa.Column('rank',        sa.Integer,      nullable=False, server_default='0'),
+        sa.Column('subscribers', sa.Integer,      nullable=False, server_default='0'),
+        sa.Column('is_nsfw',     BOOLEAN,         nullable=False, server_default='0'),
+        sa.Column('about',       sa.String(120),  nullable=False, server_default=''),
+        sa.Column('primary_tag', sa.String(32),   nullable=False, server_default=''),
+        sa.Column('category',    sa.String(32),   nullable=False, server_default=''),
+        sa.Column('avatar_url',  sa.String(1024), nullable=False, server_default=''),
+        sa.Column('description', sa.String(5000), nullable=False, server_default=''),
+        sa.Column('flag_text',   sa.String(5000), nullable=False, server_default=''),
+        sa.Column('settings',    TEXT,            nullable=False, server_default='{}'),
+        sa.Column('block_num', sa.Integer,  nullable=False ),
+
+        sa.UniqueConstraint('name', name='hive_communities_ux1'),
+        sa.Index('hive_communities_ix1', 'rank', 'id'),
+        sa.Index('hive_communities_block_num_idx', 'block_num')
+    )
+
+    sa.Table(
+        'hive_roles', metadata,
+        sa.Column('account_id',   sa.Integer,     nullable=False),
+        sa.Column('community_id', sa.Integer,     nullable=False),
+        sa.Column('created_at',   sa.DateTime,    nullable=False),
+        sa.Column('role_id',      SMALLINT,       nullable=False, server_default='0'),
+        sa.Column('title',        sa.String(140), nullable=False, server_default=''),
+
+        sa.PrimaryKeyConstraint('account_id', 'community_id', name='hive_roles_pk'),
+        sa.Index('hive_roles_ix1', 'community_id', 'account_id', 'role_id'),
+    )
+
+    sa.Table(
+        'hive_subscriptions', metadata,
+        sa.Column('id', sa.Integer, primary_key=True),
+        sa.Column('account_id',   sa.Integer,  nullable=False),
+        sa.Column('community_id', sa.Integer,  nullable=False),
+        sa.Column('created_at',   sa.DateTime, nullable=False),
+        sa.Column('block_num', sa.Integer,  nullable=False ),
+
+        sa.UniqueConstraint('account_id', 'community_id', name='hive_subscriptions_ux1'),
+        sa.Index('hive_subscriptions_ix1', 'community_id', 'account_id', 'created_at'),
+        sa.Index('hive_subscriptions_block_num_idx', 'block_num')
+    )
+
+    sa.Table(
+        'hive_notifs', metadata,
+        sa.Column('id',           sa.Integer,  primary_key=True),
+        sa.Column('type_id',      SMALLINT,    nullable=False),
+        sa.Column('score',        SMALLINT,    nullable=False),
+        sa.Column('created_at',   sa.DateTime, nullable=False),
+        sa.Column('src_id',       sa.Integer,  nullable=True),
+        sa.Column('dst_id',       sa.Integer,  nullable=True),
+        sa.Column('post_id',      sa.Integer,  nullable=True),
+        sa.Column('community_id', sa.Integer,  nullable=True),
+        sa.Column('block_num',    sa.Integer,  nullable=True),
+        sa.Column('payload',      sa.Text,     nullable=True),
+
+        sa.Index('hive_notifs_ix1', 'dst_id',                  'id', postgresql_where=sql_text("dst_id IS NOT NULL")),
+        sa.Index('hive_notifs_ix2', 'community_id',            'id', postgresql_where=sql_text("community_id IS NOT NULL")),
+        sa.Index('hive_notifs_ix3', 'community_id', 'type_id', 'id', postgresql_where=sql_text("community_id IS NOT NULL")),
+        sa.Index('hive_notifs_ix4', 'community_id', 'post_id', 'type_id', 'id', postgresql_where=sql_text("community_id IS NOT NULL AND post_id IS NOT NULL")),
+        sa.Index('hive_notifs_ix5', 'post_id', 'type_id', 'dst_id', 'src_id', postgresql_where=sql_text("post_id IS NOT NULL AND type_id IN (16,17)")), # filter: dedupe
+        sa.Index('hive_notifs_ix6', 'dst_id', 'created_at', 'score', 'id', postgresql_where=sql_text("dst_id IS NOT NULL")), # unread
+    )
+
+    return metadata
+
+
+def teardown(db):
+    """Drop all tables"""
+    build_metadata().drop_all(db.engine())
+
+def drop_fk(db):
+    db.query_no_return("START TRANSACTION")
+    for table in build_metadata().sorted_tables:
+        for fk in table.foreign_keys:
+            sql = """ALTER TABLE {} DROP CONSTRAINT IF EXISTS {}""".format(table.name, fk.name)
+            db.query_no_return(sql)
+    db.query_no_return("COMMIT")
+
+def create_fk(db):
+    from sqlalchemy.schema import AddConstraint
+    from sqlalchemy import text
+    connection = db.engine().connect()
+    connection.execute(text("START TRANSACTION"))
+    for table in build_metadata().sorted_tables:
+        for fk in table.foreign_keys:
+            connection.execute(AddConstraint(fk.constraint))
+    connection.execute(text("COMMIT"))
+
+def setup(db):
+    """Creates all tables and seed data"""
+    # initialize schema
+    build_metadata().create_all(db.engine())
+
+    # tune auto vacuum/analyze
+    reset_autovac(db)
+
+    # sets FILLFACTOR:
+    set_fillfactor(db)
+
+    # default rows
+    sqls = [
+        "INSERT INTO hive_state (block_num, db_version, steem_per_mvest, usd_per_steem, sbd_per_steem, dgpo) VALUES (0, %d, 0, 0, 0, '')" % DB_VERSION,
+        "INSERT INTO hive_blocks (num, hash, created_at) VALUES (0, '0000000000000000000000000000000000000000', '2016-03-24 16:04:57')",
+
+        "INSERT INTO hive_permlink_data (id, permlink) VALUES (0, '')",
+        "INSERT INTO hive_category_data (id, category) VALUES (0, '')",
+        "INSERT INTO hive_accounts (id, name, created_at) VALUES (0, '', '1970-01-01T00:00:00')",
+
+        "INSERT INTO hive_accounts (name, created_at) VALUES ('miners',    '2016-03-24 16:05:00')",
+        "INSERT INTO hive_accounts (name, created_at) VALUES ('null',      '2016-03-24 16:05:00')",
+        "INSERT INTO hive_accounts (name, created_at) VALUES ('temp',      '2016-03-24 16:05:00')",
+        "INSERT INTO hive_accounts (name, created_at) VALUES ('initminer', '2016-03-24 16:05:00')",
+
+        """
+        INSERT INTO
+            public.hive_posts(id, root_id, parent_id, author_id, permlink_id, category_id,
+                community_id, created_at, depth, block_num
+            )
+        VALUES
+            (0, 0, 0, 0, 0, 0, 0, now(), 0, 0);
+        """]
+    for sql in sqls:
+        db.query(sql)
+
+    sql = "CREATE INDEX hive_communities_ft1 ON hive_communities USING GIN (to_tsvector('english', title || ' ' || about))"
+    db.query(sql)
+
+    sql = """
+      DROP FUNCTION IF EXISTS find_comment_id(character varying, character varying, boolean)
+      ;
+      CREATE OR REPLACE FUNCTION find_comment_id(
+        in _author hive_accounts.name%TYPE,
+        in _permlink hive_permlink_data.permlink%TYPE,
+        in _check boolean)
+      RETURNS INT
+      LANGUAGE 'plpgsql'
+      AS
+      $function$
+      DECLARE
+        post_id INT;
+      BEGIN
+        SELECT INTO post_id COALESCE( (SELECT hp.id
+        FROM hive_posts hp
+        JOIN hive_accounts ha ON ha.id = hp.author_id
+        JOIN hive_permlink_data hpd ON hpd.id = hp.permlink_id
+        WHERE ha.name = _author AND hpd.permlink = _permlink AND hp.counter_deleted = 0
+        ), 0 );
+        IF _check AND (_author <> '' OR _permlink <> '') AND post_id = 0 THEN
+          RAISE EXCEPTION 'Post %/% does not exist', _author, _permlink;
+        END IF;
+        RETURN post_id;
+      END
+      $function$
+      ;
+    """
+    db.query_no_return(sql)
+
+    sql = """
+        DROP FUNCTION IF EXISTS find_account_id(character varying, boolean)
+        ;
+        CREATE OR REPLACE FUNCTION find_account_id(
+          in _account hive_accounts.name%TYPE,
+          in _check boolean)
+        RETURNS INT
+        LANGUAGE 'plpgsql'
+        AS
+        $function$
+        DECLARE
+          account_id INT;
+        BEGIN
+          SELECT INTO account_id COALESCE( ( SELECT id FROM hive_accounts WHERE name=_account ), 0 );
+          IF _check AND account_id = 0 THEN
+            RAISE EXCEPTION 'Account % does not exist', _account;
+          END IF;
+          RETURN account_id;
+        END
+        $function$
+        ;
+    """
+    db.query_no_return(sql)
+
+    sql = """
+          DROP FUNCTION if exists process_hive_post_operation(character varying,character varying,character varying,character varying,timestamp without time zone,timestamp without time zone)
+          ;
+          CREATE OR REPLACE FUNCTION process_hive_post_operation(
+            in _author hive_accounts.name%TYPE,
+            in _permlink hive_permlink_data.permlink%TYPE,
+            in _parent_author hive_accounts.name%TYPE,
+            in _parent_permlink hive_permlink_data.permlink%TYPE,
+            in _date hive_posts.created_at%TYPE,
+            in _community_support_start_date hive_posts.created_at%TYPE,
+            in _block_num hive_posts.block_num%TYPE)
+          RETURNS TABLE (is_new_post boolean, id hive_posts.id%TYPE, author_id hive_posts.author_id%TYPE, permlink_id hive_posts.permlink_id%TYPE,
+                         post_category hive_category_data.category%TYPE, parent_id hive_posts.parent_id%TYPE, community_id hive_posts.community_id%TYPE,
+                         is_valid hive_posts.is_valid%TYPE, is_muted hive_posts.is_muted%TYPE, depth hive_posts.depth%TYPE)
+          LANGUAGE plpgsql
+          AS
+          $function$
+          BEGIN
+
+          INSERT INTO hive_permlink_data
+          (permlink)
+          values
+          (
+          _permlink
+          )
+          ON CONFLICT DO NOTHING
+          ;
+          if _parent_author != '' THEN
+            RETURN QUERY INSERT INTO hive_posts as hp
+            (parent_id, depth, community_id, category_id,
+             root_id, is_muted, is_valid,
+             author_id, permlink_id, created_at, updated_at, sc_hot, sc_trend, active, payout_at, cashout_time, counter_deleted, block_num)
+            SELECT php.id AS parent_id, php.depth + 1 AS depth,
+                (CASE
+                   WHEN _date > _community_support_start_date THEN
+                     COALESCE(php.community_id, (select hc.id from hive_communities hc where hc.name = _parent_permlink))
+                   ELSE NULL
+                END) AS community_id,
+                COALESCE(php.category_id, (select hcg.id from hive_category_data hcg where hcg.category = _parent_permlink)) AS category_id,
+                (CASE(php.root_id)
+                   WHEN 0 THEN php.id
+                   ELSE php.root_id
+                 END) AS root_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,
+                calculate_time_part_of_hot(_date) AS sc_hot,
+                calculate_time_part_of_trending(_date) AS sc_trend,
+                _date AS active, (_date + INTERVAL '7 days') AS payout_at, (_date + INTERVAL '7 days') AS cashout_time, 0, _block_num as block_num
+            FROM hive_accounts ha,
+                 hive_permlink_data hpd,
+                 hive_posts php
+            INNER JOIN hive_accounts pha ON pha.id = php.author_id
+            INNER JOIN hive_permlink_data phpd ON phpd.id = php.permlink_id
+            WHERE pha.name = _parent_author AND phpd.permlink = _parent_permlink AND
+                   ha.name = _author AND hpd.permlink = _permlink AND php.counter_deleted = 0
+
+            ON CONFLICT ON CONSTRAINT hive_posts_ux1 DO UPDATE SET
+              --- During post update it is disallowed to change: parent-post, category, community-id
+              --- then also depth, is_valid and is_muted is impossible to change
+             --- post edit part
+             updated_at = _date,
+             active = _date
+            RETURNING (xmax = 0) as is_new_post, hp.id, hp.author_id, hp.permlink_id, (SELECT hcd.category FROM hive_category_data hcd WHERE hcd.id = hp.category_id) as post_category, hp.parent_id, hp.community_id, hp.is_valid, hp.is_muted, hp.depth
+          ;
+          ELSE
+            INSERT INTO hive_category_data
+            (category)
+            VALUES (_parent_permlink)
+            ON CONFLICT (category) DO NOTHING
+            ;
+
+            RETURN QUERY INSERT INTO hive_posts as hp
+            (parent_id, depth, community_id, category_id,
+             root_id, is_muted, is_valid,
+             author_id, permlink_id, created_at, updated_at, sc_hot, sc_trend, active, payout_at, cashout_time, counter_deleted, block_num)
+            SELECT 0 AS parent_id, 0 AS depth,
+                (CASE
+                  WHEN _date > _community_support_start_date THEN
+                    (select hc.id FROM hive_communities hc WHERE hc.name = _parent_permlink)
+                  ELSE NULL
+                END) AS community_id,
+                (SELECT hcg.id FROM hive_category_data hcg WHERE hcg.category = _parent_permlink) AS category_id,
+                0 as root_id, -- will use 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,
+                calculate_time_part_of_hot(_date) AS sc_hot,
+                calculate_time_part_of_trending(_date) AS sc_trend,
+                _date AS active, (_date + INTERVAL '7 days') AS payout_at, (_date + INTERVAL '7 days') AS cashout_time, 0, _block_num as block_num
+            FROM hive_accounts ha,
+                 hive_permlink_data hpd
+            WHERE ha.name = _author and hpd.permlink = _permlink
+
+            ON CONFLICT ON CONSTRAINT hive_posts_ux1 DO UPDATE SET
+              --- During post update it is disallowed to change: parent-post, category, community-id
+              --- then also depth, is_valid and is_muted is impossible to change
+              --- post edit part
+              updated_at = _date,
+              active = _date,
+              block_num = _block_num
+
+            RETURNING (xmax = 0) as is_new_post, hp.id, hp.author_id, hp.permlink_id, _parent_permlink as post_category, hp.parent_id, hp.community_id, hp.is_valid, hp.is_muted, hp.depth
+            ;
+          END IF;
+          END
+          $function$
+    """
+    db.query_no_return(sql)
+
+    sql = """
+          DROP FUNCTION if exists delete_hive_post(character varying,character varying,character varying)
+          ;
+          CREATE OR REPLACE FUNCTION delete_hive_post(
+            in _author hive_accounts.name%TYPE,
+            in _permlink hive_permlink_data.permlink%TYPE)
+          RETURNS TABLE (id hive_posts.id%TYPE, depth hive_posts.depth%TYPE)
+          LANGUAGE plpgsql
+          AS
+          $function$
+          BEGIN
+            RETURN QUERY UPDATE hive_posts AS hp
+              SET counter_deleted =
+              (
+                SELECT max( hps.counter_deleted ) + 1
+                FROM hive_posts hps
+                INNER JOIN hive_accounts ha ON hps.author_id = ha.id
+                INNER JOIN hive_permlink_data hpd ON hps.permlink_id = hpd.id
+                WHERE ha.name = _author AND hpd.permlink = _permlink
+              )
+            FROM hive_posts hp1
+            INNER JOIN hive_accounts ha ON hp1.author_id = ha.id
+            INNER JOIN hive_permlink_data hpd ON hp1.permlink_id = hpd.id
+            WHERE hp.id = hp1.id AND ha.name = _author AND hpd.permlink = _permlink AND hp1.counter_deleted = 0
+            RETURNING hp.id, hp.depth;
+          END
+          $function$
+          """
+    db.query_no_return(sql)
+
+    # In original hivemind, a value of 'active_at' was calculated from
+    # max
+    #   {
+    #     created             ( account_create_operation ),
+    #     last_account_update ( account_update_operation/account_update2_operation ),
+    #     last_post           ( comment_operation - only creation )
+    #     last_root_post      ( comment_operation - only creation + only ROOT ),
+    #     last_vote_time      ( vote_operation )
+    #   }
+    # In order to simplify calculations, `last_account_update` is not taken into consideration, because this updating accounts is very rare
+    # and posting/voting after an account updating, fixes `active_at` value immediately.
+
+    sql = """
+        DROP VIEW IF EXISTS public.hive_accounts_info_view;
+
+        CREATE OR REPLACE VIEW public.hive_accounts_info_view
+        AS
+        SELECT
+          id,
+          name,
+          (
+            select count(*) post_count
+            FROM hive_posts hp
+            WHERE ha.id=hp.author_id
+          ) 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'
+              )
+            )
+          ) active_at,
+          display_name,
+          about,
+          reputation,
+          profile_image,
+          location,
+          website,
+          cover_image,
+          rank,
+          following,
+          followers,
+          proxy,
+          proxy_weight,
+          lastread_at,
+          cached_at,
+          raw_json
+        FROM
+          hive_accounts ha
+          """
+    db.query_no_return(sql)
+
+    sql = """
+        DROP VIEW IF EXISTS public.hive_posts_view;
+
+        CREATE OR REPLACE VIEW public.hive_posts_view
+        AS
+        SELECT hp.id,
+          hp.community_id,
+          hp.root_id,
+          hp.parent_id,
+          ha_a.name AS author,
+          hp.active,
+          hp.author_rewards,
+          hp.author_id,
+          hpd_p.permlink,
+          hpd.title,
+          hpd.body,
+          hpd.img_url,
+          hpd.preview,
+          hcd.category,
+          hp.depth,
+          hp.promoted,
+          hp.payout,
+          hp.pending_payout,
+          hp.payout_at,
+          hp.last_payout_at,
+          hp.cashout_time,
+          hp.is_paidout,
+          hp.children,
+          0 AS votes,
+          0 AS active_votes,
+          hp.created_at,
+          hp.updated_at,
+            COALESCE(
+              (
+                SELECT SUM( v.rshares )
+                FROM hive_votes v
+                WHERE v.post_id = hp.id
+                GROUP BY v.post_id
+              ), 0
+            ) AS rshares,
+            COALESCE(
+              (
+                SELECT SUM( CASE v.rshares >= 0 WHEN True THEN v.rshares ELSE -v.rshares END )
+                FROM hive_votes v
+                WHERE v.post_id = hp.id AND NOT v.rshares = 0
+                GROUP BY v.post_id
+              ), 0
+            ) AS abs_rshares,
+            COALESCE(
+              (
+                SELECT COUNT( 1 )
+                FROM hive_votes v
+                WHERE v.post_id = hp.id AND v.is_effective
+                GROUP BY v.post_id
+              ), 0
+            ) AS total_votes,
+            COALESCE(
+              (
+                SELECT SUM( CASE v.rshares > 0 WHEN True THEN 1 ELSE -1 END )
+                FROM hive_votes v
+                WHERE v.post_id = hp.id AND NOT v.rshares = 0
+                GROUP BY v.post_id
+              ), 0
+            ) AS net_votes,
+          hpd.json,
+          ha_a.reputation AS author_rep,
+          hp.is_hidden,
+          hp.is_grayed,
+          hp.total_vote_weight,
+          ha_pp.name AS parent_author,
+          ha_pp.id AS parent_author_id,
+            ( CASE hp.depth > 0
+              WHEN True THEN hpd_pp.permlink
+              ELSE hcd.category
+            END ) AS parent_permlink_or_category,
+          hp.curator_payout_value,
+          ha_rp.name AS root_author,
+          hpd_rp.permlink AS root_permlink,
+          rcd.category as root_category,
+          hp.max_accepted_payout,
+          hp.percent_hbd,
+            True AS allow_replies,
+          hp.allow_votes,
+          hp.allow_curation_rewards,
+          hp.beneficiaries,
+            CONCAT('/', rcd.category, '/@', ha_rp.name, '/', hpd_rp.permlink,
+              CASE (rp.id)
+                WHEN hp.id THEN ''
+                ELSE CONCAT('#@', ha_a.name, '/', hpd_p.permlink)
+              END
+            ) AS url,
+          rpd.title AS root_title,
+          hp.sc_trend,
+          hp.sc_hot,
+          hp.is_pinned,
+          hp.is_muted,
+          hp.is_nsfw,
+          hp.is_valid,
+          hr.title AS role_title,
+          hr.role_id AS role_id,
+          hc.title AS community_title,
+          hc.name AS community_name,
+          hp.block_num
+          FROM hive_posts hp
+            JOIN hive_posts pp ON pp.id = hp.parent_id
+            JOIN hive_posts rp ON rp.id = hp.root_id
+            JOIN hive_accounts ha_a ON ha_a.id = hp.author_id
+            JOIN hive_permlink_data hpd_p ON hpd_p.id = hp.permlink_id
+            JOIN hive_post_data hpd ON hpd.id = hp.id
+            JOIN hive_accounts ha_pp ON ha_pp.id = pp.author_id
+            JOIN hive_permlink_data hpd_pp ON hpd_pp.id = pp.permlink_id
+            JOIN hive_accounts ha_rp ON ha_rp.id = rp.author_id
+            JOIN hive_permlink_data hpd_rp ON hpd_rp.id = rp.permlink_id
+            JOIN hive_post_data rpd ON rpd.id = rp.id
+            JOIN hive_category_data hcd ON hcd.id = hp.category_id
+            JOIN hive_category_data rcd ON rcd.id = rp.category_id
+            LEFT JOIN hive_communities hc ON hp.community_id = hc.id
+            LEFT JOIN hive_roles hr ON hp.author_id = hr.account_id AND hp.community_id = hr.community_id
+          WHERE hp.counter_deleted = 0;
+          """
+    db.query_no_return(sql)
+
+    sql = """
+          DROP FUNCTION IF EXISTS public.update_hive_posts_root_id(INTEGER, INTEGER);
+
+          CREATE OR REPLACE FUNCTION public.update_hive_posts_root_id(in _first_block_num INTEGER, _last_block_num INTEGER)
+              RETURNS void
+              LANGUAGE 'plpgsql'
+              VOLATILE
+          AS $BODY$
+          BEGIN
+
+          --- _first_block_num can be null together with _last_block_num
+          UPDATE hive_posts uhp
+          SET root_id = id
+          WHERE uhp.root_id = 0 AND (_first_block_num IS NULL OR (uhp.block_num >= _first_block_num AND uhp.block_num <= _last_block_num))
+          ;
+          END
+          $BODY$;
+          """
+    db.query_no_return(sql)
+
+    sql = """
+          DROP FUNCTION IF EXISTS public.update_hive_posts_children_count(INTEGER, INTEGER);
+
+          CREATE OR REPLACE FUNCTION public.update_hive_posts_children_count(in _first_block INTEGER, in _last_block INTEGER)
+              RETURNS void
+              LANGUAGE SQL
+              VOLATILE
+          AS $BODY$
+          UPDATE hive_posts uhp
+          SET children = data_source.children_count
+          FROM
+          (
+            WITH recursive tblChild AS
+            (
+              SELECT s.queried_parent, s.id
+              FROM
+              (SELECT h1.Parent_Id AS queried_parent, h1.id
+               FROM hive_posts h1
+               WHERE h1.depth > 0 AND h1.counter_deleted = 0
+                     AND h1.block_num BETWEEN _first_block AND _last_block
+               ORDER BY h1.depth DESC
+              ) s
+              UNION ALL
+              SELECT tblChild.queried_parent, p.id FROM hive_posts p
+              JOIN tblChild  ON p.Parent_Id = tblChild.Id
+              WHERE p.counter_deleted = 0
+            )
+            SELECT queried_parent, cast(count(1) AS int) AS children_count
+            FROM tblChild
+            GROUP BY queried_parent
+          ) data_source
+          WHERE uhp.id = data_source.queried_parent
+          ;
+          $BODY$;
+          """
+    db.query_no_return(sql)
+
+    sql = """
+        DROP VIEW IF EXISTS hive_votes_view
+        ;
+        CREATE OR REPLACE VIEW hive_votes_view
+        AS
+        SELECT
+            hv.id,
+            hv.voter_id as voter_id,
+            ha_a.name as author,
+            hpd.permlink as permlink,
+            vote_percent as percent,
+            ha_a.reputation as reputation,
+            rshares,
+            last_update,
+            ha_v.name as voter,
+            weight,
+            num_changes,
+            hv.permlink_id as permlink_id,
+            post_id,
+            is_effective
+        FROM
+            hive_votes hv
+        INNER JOIN hive_accounts ha_v ON ha_v.id = hv.voter_id
+        INNER JOIN hive_accounts ha_a ON ha_a.id = hv.author_id
+        INNER JOIN hive_permlink_data hpd ON hpd.id = hv.permlink_id
+        ;
+    """
+    db.query_no_return(sql)
+
+    sql = """
+        DROP TYPE IF EXISTS database_api_vote CASCADE;
+
+        CREATE TYPE database_api_vote AS (
+          id BIGINT,
+          voter VARCHAR(16),
+          author VARCHAR(16),
+          permlink VARCHAR(255),
+          weight NUMERIC,
+          rshares BIGINT,
+          percent INT,
+          last_update TIMESTAMP,
+          num_changes INT,
+          reputation FLOAT4
+        );
+
+        DROP FUNCTION IF EXISTS find_votes( character varying, character varying )
+        ;
+        CREATE OR REPLACE FUNCTION public.find_votes
+        (
+          in _AUTHOR hive_accounts.name%TYPE,
+          in _PERMLINK hive_permlink_data.permlink%TYPE
+        )
+        RETURNS SETOF database_api_vote
+        LANGUAGE 'plpgsql'
+        AS
+        $function$
+        DECLARE _POST_ID INT;
+        BEGIN
+        _POST_ID = find_comment_id( _AUTHOR, _PERMLINK, True);
+
+        RETURN QUERY
+        (
+            SELECT
+                v.id,
+                v.voter,
+                v.author,
+                v.permlink,
+                v.weight,
+                v.rshares,
+                v.percent,
+                v.last_update,
+                v.num_changes,
+                v.reputation
+            FROM
+                hive_votes_view v
+            WHERE
+                v.post_id = _POST_ID
+            ORDER BY
+                voter_id
+        );
+
+        END
+        $function$;
+
+        DROP FUNCTION IF EXISTS list_votes_by_voter_comment( character varying, character varying, character varying, int )
+        ;
+        CREATE OR REPLACE FUNCTION public.list_votes_by_voter_comment
+        (
+          in _VOTER hive_accounts.name%TYPE,
+          in _AUTHOR hive_accounts.name%TYPE,
+          in _PERMLINK hive_permlink_data.permlink%TYPE,
+          in _LIMIT INT
+        )
+        RETURNS SETOF database_api_vote
+        LANGUAGE 'plpgsql'
+        AS
+        $function$
+        DECLARE _VOTER_ID INT;
+        DECLARE _POST_ID INT;
+        BEGIN
+
+        _VOTER_ID = find_account_id( _VOTER, _VOTER != '' );
+        _POST_ID = find_comment_id( _AUTHOR, _PERMLINK, _AUTHOR != '' OR _PERMLINK != '' );
+
+        RETURN QUERY
+        (
+            SELECT
+                v.id,
+                v.voter,
+                v.author,
+                v.permlink,
+                v.weight,
+                v.rshares,
+                v.percent,
+                v.last_update,
+                v.num_changes,
+                v.reputation
+            FROM
+                hive_votes_view v
+            WHERE
+                ( v.voter_id = _VOTER_ID and v.post_id >= _POST_ID )
+                OR
+                ( v.voter_id > _VOTER_ID )
+            ORDER BY
+                voter_id,
+                post_id
+            LIMIT _LIMIT
+        );
+
+        END
+        $function$;
+
+        DROP FUNCTION IF EXISTS list_votes_by_comment_voter( character varying, character varying, character varying, int )
+        ;
+        CREATE OR REPLACE FUNCTION public.list_votes_by_comment_voter
+        (
+          in _VOTER hive_accounts.name%TYPE,
+          in _AUTHOR hive_accounts.name%TYPE,
+          in _PERMLINK hive_permlink_data.permlink%TYPE,
+          in _LIMIT INT
+        )
+        RETURNS SETOF database_api_vote
+        LANGUAGE 'plpgsql'
+        AS
+        $function$
+        DECLARE _VOTER_ID INT;
+        DECLARE _POST_ID INT;
+        BEGIN
+
+        _VOTER_ID = find_account_id( _VOTER, _VOTER != '' );
+        _POST_ID = find_comment_id( _AUTHOR, _PERMLINK, _AUTHOR != '' OR _PERMLINK != '' );
+
+        RETURN QUERY
+        (
+            SELECT
+                v.id,
+                v.voter,
+                v.author,
+                v.permlink,
+                v.weight,
+                v.rshares,
+                v.percent,
+                v.last_update,
+                v.num_changes,
+                v.reputation
+            FROM
+                hive_votes_view v
+            WHERE
+                ( v.post_id = _POST_ID and v.voter_id >= _VOTER_ID )
+                OR
+                ( v.post_id > _POST_ID )
+            ORDER BY
+                post_id,
+                voter_id
+            LIMIT _LIMIT
+        );
+
+        END
+        $function$;
+    """
+    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),
+        last_payout_at TIMESTAMP,
+        cashout_time TIMESTAMP,
+        is_paidout BOOLEAN,
+        children INT,
+        votes INT,
+        created_at TIMESTAMP,
+        updated_at TIMESTAMP,
+        rshares NUMERIC,
+        json TEXT,
+        is_hidden BOOLEAN,
+        is_grayed BOOLEAN,
+        total_votes BIGINT,
+        net_votes BIGINT,
+        total_vote_weight NUMERIC,
+        parent_author VARCHAR(16),
+        parent_permlink_or_category 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 NUMERIC,
+        active TIMESTAMP,
+        author_rewards BIGINT
+      )
+      ;
+
+      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$
+        DECLARE
+          __post_id INT;
+        BEGIN
+          __post_id = find_comment_id(_author,_permlink, False);
+          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.last_payout_at, hp.cashout_time, 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.net_votes, hp.total_vote_weight,
+              hp.parent_author, hp.parent_permlink_or_category, 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
+          FROM
+              hive_posts_view hp
+          WHERE
+              NOT hp.is_muted AND
+              hp.cashout_time > _cashout_time OR
+              hp.cashout_time = _cashout_time AND hp.id >= __post_id
+          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.last_payout_at, hp.cashout_time, 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.net_votes, hp.total_vote_weight,
+              hp.parent_author, hp.parent_permlink_or_category, 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
+          FROM
+              hive_posts_view hp
+          WHERE
+              NOT hp.is_muted AND
+              hp.author > _author OR
+              hp.author = _author AND hp.permlink >= _permlink
+          ORDER BY
+              hp.author ASC,
+              hp.permlink 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$
+        DECLARE
+          __root_id INT;
+          __post_id INT;
+        BEGIN
+          __root_id = find_comment_id(_root_author, _root_permlink, True);
+          __post_id = find_comment_id(_start_post_author, _start_post_permlink, True);
+          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.last_payout_at, hp.cashout_time, 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.net_votes, hp.total_vote_weight,
+            hp.parent_author, hp.parent_permlink_or_category, 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
+          FROM hive_posts_view hp
+          INNER JOIN
+          (
+          SELECT hp2.id, hp2.root_id FROM hive_posts hp2
+          WHERE NOT hp2.is_muted
+                AND hp2.root_id > __root_id
+                OR hp2.root_id = __root_id AND hp2.id >= __post_id AND hp2.id > 0
+          ORDER BY
+             hp2.root_id ASC
+            ,hp2.id ASC
+          LIMIT _limit
+          ) ds on hp.id = ds.id
+          ;
+        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
+        LANGUAGE sql
+        COST 100
+        STABLE
+        ROWS 1000
+      AS $function$
+        SELECT
+          hp.id, hp.community_id, hp.author, hp.permlink, hp.title, hp.body,
+          hp.category, hp.depth, hp.promoted, hp.payout, hp.last_payout_at, hp.cashout_time, 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.net_votes, hp.total_vote_weight,
+          hp.parent_author, hp.parent_permlink_or_category, 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
+        FROM
+          hive_posts_view hp
+          INNER JOIN
+          (
+            SELECT h.id FROM
+            hive_posts_api_helper h
+            WHERE
+              h.parent_author > _parent_author OR
+              h.parent_author = _parent_author AND ( h.parent_permlink_or_category > _parent_permlink OR
+              h.parent_permlink_or_category = _parent_permlink AND h.id >= find_comment_id(_start_post_author, _start_post_permlink, True) )
+            ORDER BY
+              h.parent_author ASC,
+              h.parent_permlink_or_category ASC,
+              h.id ASC
+            LIMIT
+              _limit
+          ) ds ON ds.id = hp.id
+        WHERE
+          NOT hp.is_muted
+          ;
+      $function$
+      ;
+
+      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$
+        DECLARE
+          __post_id INT;
+        BEGIN
+          __post_id = find_comment_id(_start_post_author, _start_post_permlink, True);
+          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.last_payout_at, hp.cashout_time, 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.net_votes, hp.total_vote_weight,
+              hp.parent_author, hp.parent_permlink_or_category, 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
+          FROM
+              hive_posts_view hp
+          WHERE
+              NOT hp.is_muted AND
+              hp.parent_author > _parent_author OR
+              hp.parent_author = _parent_author AND ( hp.updated_at < _updated_at OR
+              hp.updated_at = _updated_at AND hp.id >= __post_id )
+          ORDER BY
+              hp.parent_author ASC,
+              hp.updated_at DESC,
+              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$
+        DECLARE
+          __post_id INT;
+        BEGIN
+          __post_id = find_comment_id(_start_post_author, _start_post_permlink, True);
+          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.last_payout_at, hp.cashout_time, 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.net_votes, hp.total_vote_weight,
+              hp.parent_author, hp.parent_permlink_or_category, 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
+          FROM
+              hive_posts_view hp
+          WHERE
+              NOT hp.is_muted AND
+              -- fat node used wrong index (by_last_update) so the results are vastly different
+              hp.author > _author OR
+              hp.author = _author AND ( hp.updated_at < _updated_at OR
+              hp.updated_at = _updated_at AND hp.id >= __post_id )
+          ORDER BY
+              hp.author ASC,
+              hp.updated_at DESC,
+              hp.id ASC
+          LIMIT
+              _limit
+          ;
+        END
+        $function$
+        LANGUAGE plpgsql
+      ;
+    """
+
+    db.query_no_return(sql)
+
+    sql = """
+        DROP FUNCTION IF EXISTS score_for_account(in _account_id hive_accounts.id%TYPE)
+        ;
+        CREATE OR REPLACE FUNCTION score_for_account(in _account_id hive_accounts.id%TYPE)
+        RETURNS SMALLINT
+        AS
+        $function$
+        DECLARE
+          score SMALLINT;
+        BEGIN
+          SELECT INTO score
+             CASE
+                 WHEN rank.position < 200 THEN 70
+                 WHEN rank.position < 1000 THEN 60
+                 WHEN rank.position < 6500 THEN 50
+                 WHEN rank.position < 25000 THEN 40
+                 WHEN rank.position < 100000 THEN 30
+                 ELSE 20
+             END as score
+          FROM (
+             SELECT
+                 (
+                     SELECT COUNT(*)
+                     FROM hive_accounts ha_for_rank2
+                     WHERE ha_for_rank2.reputation > ha_for_rank.reputation
+                 ) as position
+             FROM hive_accounts ha_for_rank WHERE ha_for_rank.id = _account_id
+          ) as rank;
+          return score;
+        END
+        $function$
+        LANGUAGE plpgsql
+        ;
+    """
+    db.query_no_return(sql)
+
+    # hot and tranding functions
+
+    sql = """
+       DROP FUNCTION IF EXISTS date_diff() CASCADE
+       ;
+       CREATE OR REPLACE FUNCTION date_diff (units VARCHAR(30), start_t TIMESTAMP, end_t TIMESTAMP)
+         RETURNS INT AS $$
+       DECLARE
+         diff_interval INTERVAL;
+         diff INT = 0;
+         years_diff INT = 0;
+       BEGIN
+         IF units IN ('yy', 'yyyy', 'year', 'mm', 'm', 'month') THEN
+           years_diff = DATE_PART('year', end_t) - DATE_PART('year', start_t);
+           IF units IN ('yy', 'yyyy', 'year') THEN
+             -- SQL Server does not count full years passed (only difference between year parts)
+             RETURN years_diff;
+           ELSE
+             -- If end month is less than start month it will subtracted
+             RETURN years_diff * 12 + (DATE_PART('month', end_t) - DATE_PART('month', start_t));
+           END IF;
+         END IF;
+         -- Minus operator returns interval 'DDD days HH:MI:SS'
+         diff_interval = end_t - start_t;
+         diff = diff + DATE_PART('day', diff_interval);
+         IF units IN ('wk', 'ww', 'week') THEN
+           diff = diff/7;
+           RETURN diff;
+         END IF;
+         IF units IN ('dd', 'd', 'day') THEN
+           RETURN diff;
+         END IF;
+         diff = diff * 24 + DATE_PART('hour', diff_interval);
+         IF units IN ('hh', 'hour') THEN
+            RETURN diff;
+         END IF;
+         diff = diff * 60 + DATE_PART('minute', diff_interval);
+         IF units IN ('mi', 'n', 'minute') THEN
+            RETURN diff;
+         END IF;
+         diff = diff * 60 + DATE_PART('second', diff_interval);
+         RETURN diff;
+       END;
+       $$ LANGUAGE plpgsql IMMUTABLE
+    """
+    db.query_no_return(sql)
+
+    sql = """
+          DROP FUNCTION IF EXISTS public.calculate_time_part_of_trending(_post_created_at hive_posts.created_at%TYPE ) CASCADE
+          ;
+          CREATE OR REPLACE FUNCTION public.calculate_time_part_of_trending(
+            _post_created_at hive_posts.created_at%TYPE)
+              RETURNS double precision
+              LANGUAGE 'plpgsql'
+              IMMUTABLE
+          AS $BODY$
+          DECLARE
+            result double precision;
+            sec_from_epoch INT = 0;
+          BEGIN
+            sec_from_epoch  = date_diff( 'second', CAST('19700101' AS TIMESTAMP), _post_created_at );
+            result = sec_from_epoch/240000.0;
+            return result;
+          END;
+          $BODY$;
+    """
+    db.query_no_return(sql)
+
+    sql = """
+            DROP FUNCTION IF EXISTS public.calculate_time_part_of_hot(_post_created_at hive_posts.created_at%TYPE ) CASCADE
+            ;
+            CREATE OR REPLACE FUNCTION public.calculate_time_part_of_hot(
+              _post_created_at hive_posts.created_at%TYPE)
+                RETURNS double precision
+                LANGUAGE 'plpgsql'
+                IMMUTABLE
+            AS $BODY$
+            DECLARE
+              result double precision;
+              sec_from_epoch INT = 0;
+            BEGIN
+              sec_from_epoch  = date_diff( 'second', CAST('19700101' AS TIMESTAMP), _post_created_at );
+              result = sec_from_epoch/10000.0;
+              return result;
+            END;
+            $BODY$;
+    """
+    db.query_no_return(sql)
+
+    sql = """
+    DROP FUNCTION IF EXISTS public.calculate_rhsares_part_of_hot_and_trend(_rshares hive_votes.rshares%TYPE) CASCADE
+    ;
+    CREATE OR REPLACE FUNCTION public.calculate_rhsares_part_of_hot_and_trend(_rshares hive_votes.rshares%TYPE)
+    RETURNS double precision
+    LANGUAGE 'plpgsql'
+    IMMUTABLE
+    AS $BODY$
+    DECLARE
+        mod_score double precision;
+    BEGIN
+        mod_score := _rshares / 10000000.0;
+        IF ( mod_score > 0 )
+        THEN
+            return log( greatest( abs(mod_score), 1 ) );
+        END IF;
+        return  -1.0 * log( greatest( abs(mod_score), 1 ) );
+    END;
+    $BODY$;
+    """
+    db.query_no_return(sql)
+
+    sql = """
+    DROP FUNCTION IF EXISTS public.calculate_hot(hive_votes.rshares%TYPE, hive_posts.created_at%TYPE)
+    ;
+    CREATE OR REPLACE FUNCTION public.calculate_hot(
+        _rshares hive_votes.rshares%TYPE,
+        _post_created_at hive_posts.created_at%TYPE)
+    RETURNS hive_posts.sc_hot%TYPE
+    LANGUAGE 'plpgsql'
+    IMMUTABLE
+    AS $BODY$
+    BEGIN
+        return calculate_rhsares_part_of_hot_and_trend(_rshares) + calculate_time_part_of_hot( _post_created_at );
+    END;
+    $BODY$;
+    """
+    db.query_no_return(sql)
+
+    sql = """
+          DO $$
+          BEGIN
+            EXECUTE 'ALTER DATABASE '||current_database()||' SET join_collapse_limit TO 16';
+            EXECUTE 'ALTER DATABASE '||current_database()||' SET from_collapse_limit TO 16';
+          END
+          $$;
+          """
+    db.query_no_return(sql)
+
+    sql = """
+    DROP FUNCTION IF EXISTS public.calculate_tranding(hive_votes.rshares%TYPE, hive_posts.created_at%TYPE)
+    ;
+    CREATE OR REPLACE FUNCTION public.calculate_tranding(
+        _rshares hive_votes.rshares%TYPE,
+        _post_created_at hive_posts.created_at%TYPE)
+    RETURNS hive_posts.sc_trend%TYPE
+    LANGUAGE 'plpgsql'
+    IMMUTABLE
+    AS $BODY$
+    BEGIN
+        return calculate_rhsares_part_of_hot_and_trend(_rshares) + calculate_time_part_of_trending( _post_created_at );
+    END;
+    $BODY$;
+    """
+    db.query_no_return(sql)
+
+    sql = """
+        DROP FUNCTION IF EXISTS public.max_time_stamp() CASCADE;
+        CREATE OR REPLACE FUNCTION public.max_time_stamp( _first TIMESTAMP, _second TIMESTAMP )
+        RETURNS TIMESTAMP
+        LANGUAGE 'plpgsql'
+        IMMUTABLE
+        AS $BODY$
+        BEGIN
+          IF _first > _second THEN
+               RETURN _first;
+            ELSE
+               RETURN _second;
+            END IF;
+        END
+        $BODY$;
+        """
+    db.query_no_return(sql)
+
+    sql = """
+          DROP FUNCTION IF EXISTS public.update_hive_posts_api_helper(INTEGER, INTEGER);
+
+          CREATE OR REPLACE FUNCTION public.update_hive_posts_api_helper(in _first_block_num INTEGER, _last_block_num INTEGER)
+            RETURNS void
+            LANGUAGE 'plpgsql'
+            VOLATILE
+          AS $BODY$
+          BEGIN
+          IF _first_block_num IS NULL OR _last_block_num IS NULL THEN
+            -- initial creation of table.
+
+            INSERT INTO hive_posts_api_helper
+            (id, author, parent_author, parent_permlink_or_category)
+            SELECT hp.id, hp.author, hp.parent_author, hp.parent_permlink_or_category
+            FROM hive_posts_view hp
+            ;
+          ELSE
+            -- Regular incremental update.
+            INSERT INTO hive_posts_api_helper
+            (id, author, parent_author, parent_permlink_or_category)
+            SELECT hp.id, hp.author, hp.parent_author, hp.parent_permlink_or_category
+            FROM hive_posts_view hp
+            WHERE hp.block_num BETWEEN _first_block_num AND _last_block_num AND
+                   NOT EXISTS (SELECT NULL FROM hive_posts_api_helper h WHERE h.id = hp.id)
+            ;
+          END IF;
+
+          END
+          $BODY$
+          """
+    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)
+    sql = """
+        DROP FUNCTION IF EXISTS public.calculate_notify_vote_score(_payout hive_posts.payout%TYPE, _abs_rshares hive_posts_view.abs_rshares%TYPE, _rshares hive_votes.rshares%TYPE) CASCADE
+        ;
+        CREATE OR REPLACE FUNCTION public.calculate_notify_vote_score(_payout hive_posts.payout%TYPE, _abs_rshares hive_posts_view.abs_rshares%TYPE, _rshares hive_votes.rshares%TYPE)
+        RETURNS INT
+        LANGUAGE 'sql'
+        IMMUTABLE
+        AS $BODY$
+            SELECT CASE
+                WHEN ((( _payout )/_abs_rshares) * 1000 * _rshares < 20 ) THEN -1
+                ELSE LEAST(100, (LENGTH(CAST( ( (( _payout )/_abs_rshares) * 1000 * _rshares ) as text)) - 1) * 25)
+            END;
+        $BODY$;
+    """
+
+    db.query_no_return(sql)
+
+    sql = """
+        DROP FUNCTION IF EXISTS notification_id(in _block_number INTEGER, in _notifyType INTEGER, in _id INTEGER)
+        ;
+        CREATE OR REPLACE FUNCTION notification_id(in _block_number INTEGER, in _notifyType INTEGER, in _id INTEGER)
+        RETURNS BIGINT
+        AS
+        $function$
+        BEGIN
+        RETURN CAST( _block_number as BIGINT ) << 32
+               | ( _notifyType << 16 )
+               | ( _id & CAST( x'00FF' as INTEGER) );
+        END
+        $function$
+        LANGUAGE plpgsql IMMUTABLE
+        ;
+    """
+    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.
+
+    We use a scale factor of 0 and specify exact threshold tuple counts,
+    per-table, in the format (autovacuum_threshold, autoanalyze_threshold)."""
+
+    autovac_config = { #    vacuum  analyze
+        'hive_accounts':    (50000, 100000),
+        'hive_posts':       (2500, 10000),
+        'hive_post_tags':   (5000, 10000),
+        'hive_follows':     (5000, 5000),
+        'hive_feed_cache':  (5000, 5000),
+        'hive_blocks':      (5000, 25000),
+        'hive_reblogs':     (5000, 5000),
+        'hive_payments':    (5000, 5000),
+    }
+
+    for table, (n_vacuum, n_analyze) in autovac_config.items():
+        sql = """ALTER TABLE %s SET (autovacuum_vacuum_scale_factor = 0,
+                                     autovacuum_vacuum_threshold = %s,
+                                     autovacuum_analyze_scale_factor = 0,
+                                     autovacuum_analyze_threshold = %s)"""
+        db.query(sql % (table, n_vacuum, n_analyze))
+
+
+def set_fillfactor(db):
+    """Initializes/resets FILLFACTOR for tables which are intesively updated"""
+
+    fillfactor_config = {
+        'hive_posts': 70,
+        'hive_post_data': 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'))
+
+def execute_sql_script(query_executor, path_to_script):
+    """ 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;
+          will return something like: (0, 18, Decimal('0.000000'), Decimal('0.000000'), Decimal('0.000000'), '')
+    """
+    try:
+        sql_script = None
+        with open(path_to_script, 'r') as sql_script_file:
+            sql_script = sql_script_file.read()
+        if sql_script is not None:
+            return query_executor(sql_script)
+    except Exception as ex:
+        log.exception("Error running sql script: {}".format(ex))
+        raise ex
+    return None